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.
Post Reply
cdmak
User
Posts: 27

Find one record and get data and load to variables

Post by cdmak »

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"];


arbei
User
Posts: 9284

Post by arbei »

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.


cdmak
User
Posts: 27

Post by cdmak »

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);


kirondedshem
User
Posts: 642

Post by kirondedshem »

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
}


cdmak
User
Posts: 27

Post by cdmak »

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
User
Posts: 27

Post by cdmak »

Hello kirondedshem,

I changed ew_ExecuteRows to ew_ExecuteRow and it worked I made a typo, thank you for your help very much appreciated.


kirondedshem
User
Posts: 642

Post by kirondedshem »

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


cdmak
User
Posts: 27

Post by cdmak »

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.


Post Reply