How to assign individual variables to individual rows from a multiple-row result query?

PHP
1

The following code gives me multiple results

$sql = mysql_query("
SELECT * 
FROM title, description
WHERE id = description_id AND genre = 'fiction' AND description IS NOT NULL");

$num_rows = mysql_num_rows($sql);

while($row = mysql_fetch_array($sql))
 
{
echo $row['title'] . "<br>";
}

But how do I assign a variable to each individual row?

3

Well, you could create an array then build it up as part of your while() loop. I know PDO has a fetchall() method which would return the rows into an array.

You should look at dumping the old-style mysql calls in any case, and look at switching to mysqli or PDO - many threads on here about it, the older calls are in the process of being removed.

4

Isn’t there a way to simply assign a variable to each result row / iteration of the current loop?

result row: title 1
result row: title 2
… etc.

Instead of building an array out of the result rows, and assigning the array[1] a variable and array[2] a variable, isn’t there a way as the current while loop cycles through the itself that I could simply instruct the loop to assign $thisVariable1 to the first result and $thisVariable2 to the second result so on and so forth until after, say, 100 results, I have 100 different variables that I can then use and call upon.

It seems as if I’m traveling to California in a PHP car, and to get there, I always I have to head first to Rhode Island.

5

The reasoning for you wanting to do this is unclear. I would think having 100 variables for each field would be a total nightmare to use. It almost sounds as if you are planning on hand coding the display with these variables instead of looping through a row to dynamically build your output (table, list… whatever).

6

Perhaps. But it can’t be more of a nightmare than figuring out how to do it in the first place. – finally got the array building and exploding and variable assignment working. Thanks Droopsnoot!

7

You just need to design the display for a single row… A rough example.

echo '<tr>
    <td> 
    <span class="title"><a href="' . $row['url'] . '">' . $row['title'] . '</a></span><br />
    <span class="description">' . $row['description'] . '</span><br />
    <a href="' . $row['url'] . '">Read more</a>
    </td>
</tr>'."\r";

Then loop this to display all items.

8

I believe you can, but as @Drummin said above, it’s hard to see why you would want to do that in preference to using an array. You could do something like this:

$count = 0;
$num_rows = mysql_num_rows($sql);
while($row = mysql_fetch_array($sql))
{
${"title" . $count} = $row['title'];
$count += 1;
echo $row['title'] . "<br>";
}

and at the end you’d have variables $title1, $title2, $title3 and so on. But it’s hard to see what would make you want to use that over an array, given the other advantages of an array (such as being able to pass it to a function in its entirety, where the above would require each individual variable to be specified or made global). The only way I could think you’d be able to do that for each entire result row would be to use an array instead, I don’t know if this would work:

$count = 0;
$num_rows = mysql_num_rows($sql);
while($row = mysql_fetch_array($sql))
{
${"rslt" . $count}[] = $row;
echo $row['title'] . "<br>";
}

… which might give you a series of arrays called $rslt1, $rslt2, but now you’ve ended up with arrays anyway, so you might as well just use a two-dimensional array and make it clearer.

But also - have a look at PDO for your database access - in particular this would just give you an array of arrays containing all the results: http://php.net/manual/en/pdostatement.fetchall.php though of course that isn’t always desirable if you’re going to have a lot of results.

(I realise you’ve solved the issue now, so this post is probably irrelevant).

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.