MySQL results into variables

[kencook]

I can’t figure this out. How do I do something like:

[php]$query = “SELECT fname FROM table LIMIT 4”;
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
$result1 = “Bob”;
$result2 = “Sue”;
$result3 = “Ganesh”;
$result4 = “Ali”;
}[/php]

I need to get four id numbers/names (whatever) from a table and assign those to four distinct variables so I can use those later on the same page to perform other queries like this:

[php]$query = “SELECT * FROM access WHERE userID = ‘$result1’”;
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
$id = $row[‘id’];
$lastDate = $row[‘date’];
}

$query = “SELECT * FROM access WHERE userID = ‘$result2’”;
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
$id = $row[‘id’];
$lastDate = $row[‘date’];
}

$query = “SELECT * FROM access WHERE userID = ‘$result3’”;
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
$id = $row[‘id’];
$lastDate = $row[‘date’];
}

$query = “SELECT * FROM access WHERE userID = ‘$result4’”;
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
$id = $row[‘id’];
$lastDate = $row[‘date’];
}[/php]

[m11]

I think you need to learn to utilize arrays. For example:

[php]
// create array of names to query
$names = array();
$query = “SELECT fname FROM table LIMIT 4”;
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
$names[] = $row[‘fname’];
}

// query results for all names
$results = array();
$query = “SELECT * FROM access WHERE userID IN(’” . implode("’,’" . $names) . “’)”;
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
$results[$row[‘userID’]] = $row; // sort results by name
}

// Now you have for example $results[‘Bob’][‘id’]
[/php]

[kencook]

Okay - I see that and I have used arrays before - not immensely. So how do I access those results once they are output? I mean how can I call them/refer to them in the future (within the same web page)?

[m11]

By accessing the array key. Did you see my comment at the end of the code?

[kencook]

Yes and thank you. I know how to refer to the results INSIDE of the loop. I don’t know how to store them into a new string to be used outside the loop - in a different snippet on the same page or even put them into a hidden field or $_GET link. And I probably have been working on this for too long and I’m getting code blind and brain fog.

[kencook]

Figured it out - missing link was declaring global variables:

[php]global $proid;
global $code;
$result = mysql_query(“SELECT * FROM videos WHERE subcatid = ‘$subcatid’ LIMIT 4”);
while($row = mysql_fetch_array($result)){
$proid[] = $row[‘proid’];
$code[] = $row[‘code’];
}[/php]

[m11]

Figured it out - missing link was declaring global variables

Sure, if that code is inside a function A loop is not a function so there is no variable scope needed there.

[php]
$proid = array();
$code = array();

$result = mysql_query(“SELECT * FROM videos WHERE subcatid = ‘$subcatid’ LIMIT 4”);
while($row = mysql_fetch_array($result)){
$proid[] = $row[‘proid’];
$code[] = $row[‘code’];
}

// $proid and $code can be used here
echo “

”;

print_r($pro_id);

print_r($code);

echo “

”;
[/php]

Also - I don’t know what these two arrays are intended for, but considering they are from the same mysql row, you might be better off using a single array. For example:

[php]
$result = mysql_query(“SELECT * FROM videos WHERE subcatid = ‘$subcatid’ LIMIT 4”);
while($row = mysql_fetch_array($result)){
$proid[$row[‘proid’]] = $row[‘code’];
}
[/php]

This way if you need to get the proid of a code then you could simply use array_search() OR if you already know the proid you can access the array directly to get the code value.

[kencook]

I’m assigning those to variables to use later on the page in a separate script. I’m mocking something up for modular assembly later. I’m sure there is a cleaner way to do it - I just don’t know what it is - so I’m storing those in globals to use on other parts of the page and hoping it’s easier to read when I turn it over:

$proid1 = $proid[0];