Find one record and get data and load to variables

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.

Find one record and get data and load to variables

Postby cdmak » Tue Jan 09, 2018 4:44 am

Hello,

I'm trying to get the data from one record and load it into variables. Thank you for any help much appreciated.

$db = &DbHelper();
$getUserData = $db->ExecuteRow("SELECT* FROM users WHERE user_id='$current_user_id'");
$first_name = $getUserData["first_name"];
$last_name = $getUserData["first_name"];
$email_address = $getUserData["email_address"];
cdmak
 
Posts: 24
Joined: Tue Nov 22, 2016 10:51 am

Re: Find one record and get data and load to variables

Postby arbei » Tue Jan 09, 2018 12:05 pm

If the field "first_name", "last_name" and "email_address" are in the user table, you can use the function CurrentUserInfo("<Field name>"); to get the corresponding field value.

Read help file topic: "Server Events and Client Scripts" -> "Some Global Functions" for more information.
arbei
 
Posts: 1499
Joined: Wed Sep 09, 2015 10:13 am

Re: Find one record and get data and load to variables

Postby cdmak » Wed Jan 10, 2018 2:38 pm

Thank you that worked great, I went to the help menu looking for table query but can not find anything like building a table query, since I have to access another table by referencing the USER_ID field in it and pull the information from it and insert it into profile table.

ew_ExecuteRows($sql [,$dbname])

How would I do a query in PHPMaker like a regular old mysqli query

http://php.net/manual/en/mysqli-result.fetch-assoc.php

$ZIPCODE = CurrentUserInfo("zipcode");
$zipQwy = mysqli_query($conn, "SELECT * FROM states WHERE zipcode='$ZIPCODE")
or die(mysqli_error());
$row_zips = mysqli_fetch_assoc($zipQwy);

$row_zips['city'];

mysqli_free_result($zipQwy);
cdmak
 
Posts: 24
Joined: Tue Nov 22, 2016 10:51 am

Re: Find one record and get data and load to variables

Postby kirondedshem » Wed Jan 10, 2018 2:52 pm

If you want only the city value of the first row only then use ew_ExecuteRow, like this.
NOTE:ew_ExecuteRow returns the first row in a rcordset you can read about other functions like ew_ExecuteScalar, ew_ExecuteRow, ew_ExecuteRows, ew_LoadRecordset, ew_ExecuteHtml which ideally can handle all database opretaions for you.

$ZIPCODE = CurrentUserInfo("zipcode");
//get the first row in record set that matches this query
$state_row = ew_ExecuteRow("SELECT * FROM states WHERE zipcode='$ZIPCODE");
//check if we have a result beofre trying to access it
if($state_row)
{
//get value by referencing column name
$the_city = $state_row["city"];
//perform toher operations here
}

HINT: If I only and ONLY need the city then I can also substute the query line to only return the city for me like
$ZIPCODE = CurrentUserInfo("zipcode");
//get the city value only
$state_city = ew_ExecuteScalar("SELECT city FROM states WHERE zipcode='$ZIPCODE");
//check if we have a result beofre trying to access it
if($state_city)
{
//get value by referencing column name
$the_city = $state_city;
//perform toher operations here
}
kirondedshem
 
Posts: 331
Joined: Sat Jan 28, 2017 7:13 pm

Re: Find one record and get data and load to variables

Postby cdmak » Wed Jan 10, 2018 3:34 pm

Thank you kirondedshem,

Sorry I did not specify that I wanted to access more fields in the row than just one, there are several. I tried building this and it did not work I'm not sure why it will not pull the data from the states table, the zip code is in there and will make a match. I have this script in the Global > Table-Specific > Other > User_LoggedIn

$ZIPCODE = CurrentUserInfo("zipcode");
//get the first row in record set that matches this query
$state_row = ew_ExecuteRow("SELECT * FROM states WHERE zip_code='$ZIPCODE");
//check if we have a result before trying to access it
if($state_row)
{
//get value by referencing column name
$the_city = $state_row["city"];
$the_state = $state_row["state"];
//perform other operations here
}
cdmak
 
Posts: 24
Joined: Tue Nov 22, 2016 10:51 am

Re: Find one record and get data and load to variables

Postby cdmak » Wed Jan 10, 2018 3:54 pm

Hello kirondedshem,

I changed ew_ExecuteRows to ew_ExecuteRow and it worked I made a typo, thank you for your help very much appreciated.
cdmak
 
Posts: 24
Joined: Tue Nov 22, 2016 10:51 am

Re: Find one record and get data and load to variables

Postby kirondedshem » Wed Jan 10, 2018 3:59 pm

Sorry I dint realise this, but during this event the CurrentUserInfor object has not been loaded yet, BUt they provide you the username in the $usr object, so you can use this to determine which user this is and proceed , like this

//get zip code of current user by username which is give in $usr
//assume your user table is calleed user_table
$ZIPCODE = ew_ExecuteScalar("select zip_code from user_table where username = '$usr' ");
//get the first row in record set that matches this query
$state_row = ew_ExecuteRow("SELECT * FROM states WHERE zip_code='$ZIPCODE");
//check if we have a result before trying to access it
if($state_row)
{
//get value by referencing column name
$the_city = $state_row["city"];
$the_state = $state_row["state"];
//perform other operations here
}

>I did not specify that I wanted to access more fields in the row than just one
The ew_execte row returns al the fields available in the select query so just use the column anme to refernece a vlaue, so the way youve done it is okay
kirondedshem
 
Posts: 331
Joined: Sat Jan 28, 2017 7:13 pm

Re: Find one record and get data and load to variables

Postby cdmak » Wed Jan 10, 2018 4:42 pm

Thanks kirondedshem,

Thank you so much for the example, I'm reading through the manual too thanks for pointing me there, little bit learning curve but I can see how powerful PHPMaker is. I don't want to resort to using mysqli queries as you recommend I'm following your advice to keep the coding native to PHPMaker.

Thanks again for your help.
cdmak
 
Posts: 24
Joined: Tue Nov 22, 2016 10:51 am


Return to General Discussion (PHPMaker)