Where statement syntax 4

[Domino2]

I have searched posts, but cannot find an answer to my problem.

$result = mysql_query("SELECT * FROM person
WHERE FirstName='Peter'");

How can I modify the above to replace the name Peter with a session variable already filled. ie $FirstName.

Thanks

 

[vacunita]

Since you are using double quotes " you can just enter the variable into the query normally.

$result = mysql_query("SELECT * FROM person
WHERE FirstName='$FirstName'");

You could also concatenate the value such as:

$result = mysql_query("SELECT * FROM person
WHERE FirstName='[red]" . $FirstName . "'"[/red]);

Of course your variable would have to be pre-filled. and just to clarify your $FirstName is not a session variable.

Session variables are usually referenced as $_SESSION['nameofvar']




----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[progman1010]

and of course if you're using the $_SESSION array:

$result = mysql_query("SELECT * FROM person
WHERE FirstName='{$_SESSION['Firstname']}'");

 

[Domino2]

Many thanks both. Have a star each. What would be the best way to get other table fields in a query, stored that are destined for display in a page of fields. Do I put the query result fields into individual variables or can a session array be filled that can be used in the form ie A(1),A2)etc

 

[jpadie]

mysql_fetch_assoc() will return an associative array of fieldnames=>fieldvalues

or have i misunderstood?

 

[Domino2]

No you are correct, however I need to find the practicle way of assigning whatever is in the recordset to a page with database fields. Is it best to use an array or make each field = $Name, $Company etc Hope I have not confused you, regards

 

[jpadie]

it's best to use the array that comes out of the mysql_fetch_* functions. otherwise you are duplicating memory registers for no reason.

if you want a quick method of outputting information try this as a cheap form of dynamic output. it doesn't differentiate between data types. for this you would need to pre-profile your forms. check out HTML_QuickForm for ways to do this (pear.php.net).

<?php
if (mysql_num_rows($result) > 0){
	echo "<table border=\"1\">\r\n";
	while ($row = mysql_fetch_assoc($result)){
		//start the row
		echo "\t<tr>\r\n";
		//if this is the first row, then print the fieldnames
		if (empty($flag)){
			foreach ($row as $field=>$value){
				echo "\t\t<th>$field</th>\r\n";
			}
			//set the flag to stop the fieldnames from printing again
			$flag = true;
			// restart the row
			echo "\t</tr>\r\n\t<tr>\r\n";
		}
		//start the actual data
		foreach ($row as $field=>$value){
			echo "\t\t<td>$value</td>\r\n";
		}
		//end the row
		echo "\t</tr>\r\n";
	} //end the while loop
	echo "</table>\r\n";
} else {
	echo "No Data found";
}
?>

 

[Domino2]

Many thanks, however I am not using a table but have defined input boxes. As it is only 1 records worth being displayed at a time, I will adopt session arrays in a loop somehow during the query. There are only about 20 fields. So thanks for the advice. Regards

 

[jpadie]

my code simply showed how to output any dynamic html. there is no requirement that you use a table. below is code using tables and input boxes

it would not be a good idea to output to a session array and then to the screen. it would be a pointless intermediate step (unless you have another purpose).

<?php
if (mysql_num_rows($result) > 0){
    echo "<table border=\"1\">\r\n";
    while ($row = mysql_fetch_assoc($result)){
        foreach ($row as $field=>$value){
			//start the row
			echo "\t<tr>\r\n";
			//start the actual data
			echo "\t\t<td>$field</td><td><input type=\"text\" name=\"$field\" value=\"$value\" /></td></tr>\r\n";
        	echo "\t</tr>\r\n";
		}
    } //end the while loop
    echo "</table>\r\n";
} else {
    echo "No Data found";
}
?>

 

[Domino2]

Many thanks, that looks a clear way to fill my page. I will move my query code to the same page and adopt your idea. Very much appreciated.

 

[Domino2]

Can someone see why I am getting more than 1 record selected with a given barcode number. Thanks

$BC=ECS000002163;

// Make a MySQL Connection

// Construct our join query
$query = "SELECT PARENT.ID1, PARENT.Barcode, CHILD.ID1, CHILD.ID2, CHILD.NAME ".
"FROM PARENT LEFT JOIN CHILD ".
"ON PARENT.ID1 = CHILD.ID1 ";
"WHERE Barcode='$BC'";

$result = mysql_query($query) or die(mysql_error());

if (mysql_num_rows($result) > 0){
echo "<table border=\"1\">\r\n";
while ($row = mysql_fetch_assoc($result)){
foreach ($row as $field=>$value){
//start the row
echo "\t<tr>\r\n";
//start the actual data
echo "\t\t<td>$field</td><td><input type=\"text\" name=\"$field\" value=\"$value\" /></td></tr>\r\n";
echo "\t</tr>\r\n";
}
} //end the while loop
echo "</table>\r\n";
} else {
echo "No Data found";
}

 

[Domino2]

Sorry, just spotted offending ; and missing .

"ON PARENT.ID1 = CHILD.ID1 ".
"WHERE Barcode='$BC'";

Thanks

 

[Domino2]

Spoke too soon. Now only the selected barcode is in the result, but duplicate rows? Thanks

 

[Domino2]

Guess its because it's looking at rows where I am wanting fields. I have tried to amend below but still not working.

echo "$num_fields\n";


if (mysql_num_fields($result) > 0){
echo "<table border=\"1\">\r\n";
while ($fields = mysql_fetch_assoc($result)){
foreach ($fields as $fields=>$value){
//start the row
echo "\t<tr>\r\n";
//start the actual data
echo "\t\t<td>$fields</td><td><input type=\"text\" name=\"$fields\" value=\"$value\" /></td></tr>\r\n";
echo "\t</tr>\r\n";
}
} //end the while loop
echo "</table>\r\n";
} else {
echo "No Data found";
}

 

[ingresman]

might you have duplicate barcodes in your tables.
try a select count(*) from parent where barcode = "$BC

 

[ingresman]

or the join on parent.id1 with child.id1 returns many rows e.g. more than one child so if child has 5 rows with the same id as the parent you will get back 5 rows. You coukd add a distinct clause perhaps to return only one row.

 

[Domino2]

Many thanks, that was happening. The distinct clause sorted it.