php mysql query help needed

ohcarol · 03-13-2008, 10:54 AM

Hello all,

My sql table "users" structure looks like this

-----------------------------
id | name | attribute | value
-----------------------------
01 | tori | callerid | 546878
02 | tori | callerid | 789562
------------------------------

I would like to get the attribute value printed, mysql sql queries is like:

$sql="select value from user where attribute='callerid' and name='tori'";
$query=mysql_query($sql);
$result=mysql_num_rows($query);
while(list($caller1, $caller2)=mysql_fetch_row($query)){
$mac1=$caller1;
$mac2=$caller2;
}
echo "$mac1";
echo "$mac2";

=================
From the above statement I am getting only one value? What I did wrong can anyone guide me? I am not a programmer?

I want to grab the individual Value so that I can edit individually the callerid value.

BrianK · 03-13-2008, 12:55 PM

Quote:

Originally Posted by ohcarol

From the above statement I am getting only one value? What I did wrong can anyone guide me? I am not a programmer?

I want to grab the individual Value so that I can edit individually the callerid value.

you're doing the echo outside of the while loop, so you're only seeing the last assignment. do the echo inside the while loop & you'll see both values.

edited to add: also, do this query on a command line to see what it returns... it will only return one item, so :

list($caller1, $caller2)=mysql_fetch_row($query)

will only assign anything to $caller1, but will assign it each time through the loop.

ohcarol · 03-13-2008, 10:51 PM

Yes, Briank while doing echo inside the while loop I will get the both value on the same row.
How is it possible to get seperate value outside while loop?

chrism01 · 03-13-2008, 11:28 PM

Store the results in an array

ohcarol · 03-14-2008, 03:03 AM

I did this to store result in array didn't work.

$sql="select value from user where attribute='callerid' and name='tori'";
$query=mysql_query($sql);
$result=mysql_num_rows($query);
while(list($caller1, $caller2)=mysql_fetch_row($query)){
$mac1=$caller1;
$mac2=$caller2;
}
echo "$mac1";
echo "$mac2";

konsolebox · 03-14-2008, 04:24 AM

please try this one.. i haven't tested this yet since i don't have the mysql's

Code:

$sql="select value from user where attribute='callerid' and name='tori'";
$query=mysql_query($sql);
$mac1 = array();
$mac2 = array();
while (list($caller1, $caller2) = mysql_fetch_row($query)) {
	array_push($mac1, $caller1);
	array_push($mac2, $caller2);
}
echo implode($mac1, " ")."\n";
echo implode($mac2, " ")."\n";

ohcarol · 03-14-2008, 11:12 AM

Quote:

Originally Posted by konsolebox

please try this one.. i haven't tested this yet since i don't have the mysql's

Code:

$sql="select value from user where attribute='callerid' and name='tori'";
$query=mysql_query($sql);
$mac1 = array();
$mac2 = array();
while (list($caller1, $caller2) = mysql_fetch_row($query)) {
	array_push($mac1, $caller1);
	array_push($mac2, $caller2);
}
echo implode($mac1, " ")."\n";
echo implode($mac2, " ")."\n";

Thanks, some what to the points. While with the above statement it only displays the result with echo command. But can't display the value with following php code.

echo <<<EOM
<tr>
<td>MAC Use
<select name="macuse" size="1">
<option value="N">N</option>
<option selected value="Y">Y</option>
</select>
<input type="text" name="mac" size="12" value="$mac1" ><input type="submit" value="Update" name="Update"></td>
EOM;

BrianK · 03-14-2008, 01:12 PM

Quote:

Originally Posted by ohcarol

Thanks, some what to the points. While with the above statement it only displays the result with echo command. But can't display the value with following php code.

echo <<<EOM
<tr>
<td>MAC Use
<select name="macuse" size="1">
<option value="N">N</option>
<option selected value="Y">Y</option>
</select>
<input type="text" name="mac" size="12" value="$mac1" ><input type="submit" value="Update" name="Update"></td>
EOM;

here's an example:

PHP Code:



$arr = array();
for ($i=0; $i<10; ++$i) {
  $arr[] = $i;
}

for ($j=0; $j<count($arr); ++$j) {
  echo "arr[$j] = $arr[$j]<br>\n";
}

which outputs:

Code:

arr[0] = 0
arr[1] = 1
arr[2] = 2
arr[3] = 3
arr[4] = 4
arr[5] = 5
arr[6] = 6
arr[7] = 7
arr[8] = 8
arr[9] = 9

as I stated earlier, it's not correct to assign the mysql fetch to an array of two elements, being that it only returns one.

konsolebox's example was pretty close. Here's a [imho] better version:

PHP Code:



$sql="select value from user where attribute='callerid' and name='tori'";
$query=mysql_query($sql);
$mac1 = array();
while (list($caller1) = mysql_fetch_row($query)) {
    $mac1[] = $caller1
}

then see my above example on how to access that stuff later.

note that "array_push($arr,$thing)" is essentially the same as "$arr[] = $thing"

One more note: it's arguable that you could make your select box inside of the while loop. Just put the select open before the loop, the options in the loop then your select close after the loop. That said, it's probably more readable to do what you're asking, i.e. store the mysql return in an array then use the array to create the selection box.

konsolebox · 03-14-2008, 11:00 PM

Code:

$sql="select value from user where attribute='callerid' and name='tori'";
$query=mysql_query($sql);
$mac1 = array();
while (list($caller1) = mysql_fetch_row($query)) {
    $mac1[] = $caller1
}

yes this is truly a better version. perhaps if you want to show the output in html you can append this:

Code:

foreach ($mac1 as $m) {
?>
	<tr><td>
		MAC Use
		<select name="macuse" size="1">
			<option value="N">N</option>
			<option selected value="Y">Y</option>
		</select>
		<input type="text" name="mac" size="12" value="<?php echo $m; ?>" />
		<input type="submit" value="Update" name="Update" />
	</td></tr>
<?php
}

please check for typos just in case.. i haven't tested this yet.

ohcarol · 03-16-2008, 03:28 AM

Thanks all for your suggestion. I will try it out.