Select multiple values from the same row?

Question

I have a script and I want to return 5 values from the database that have the same category but it is only returning one. Any ideas?

Here's the script:

 $result = mysql_query("SELECT Category FROM GameData
        WHERE Title=\"$title\" ");
//returns category to search in next select
    $row= mysql_fetch_array($result);
    $results = mysql_query("SELECT Title FROM GameData
    WHERE Category=\"$row[0]\" LIMIT 5 ");
    $rows= mysql_fetch_array($results);
    print_r($rows); //returns $title from first select query

I'm new to databases and MySQL so any help would be much appreciated.

I would suggest you use something like PHP PDO instead of mysql_x. It's newer, provides db abstraction and with prepared statements you can prevent sql injection — CJD
– CJD, Commented Feb 26, 2011 at 17:23
Your code is unfortunately not efficient, if you are new to databases, learn them from beginning in right way. Use join's. — mailo
– mailo, Commented Feb 26, 2011 at 17:27

Shakti Singh · Accepted Answer · 2011-02-26 17:22:32Z

4

mysql_fetch_array just fetch one row in one call use loop to fetch multiple records

while($row= mysql_fetch_array($results))
{

    print_r($row); //returns $title from first select query
}

answered Feb 26, 2011 at 17:22

Shakti Singh

86.7k21 gold badges142 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ChrisJ · Accepted Answer · 2011-02-26 17:21:53Z

3

You must loop over all the results: mysql_fetch_array returns one result row, so you have to call it multiple times:

while($row = mysql_fetch_array($results)) {
    // here process ONE row
}

answered Feb 26, 2011 at 17:21

ChrisJ

5,28128 silver badges20 bronze badges

1 Comment

Matt Over a year ago

Thanks a ton. Thought it might be something with a loop but haven't used any loop in php other than the for loop so didn't know what to use. Thanks again :)

Pascal MARTIN · Accepted Answer · 2011-02-26 17:21:50Z

1

mysql_fetch_array will only fetch one row : if you want to fetch several rows, you'll have to call mysql_fetch_array several times -- in a loop, typically.

For a couple of examples, you can take a look at its manual page ; but, in your case, you'll probably want to use something like this :

$results = mysql_query("SELECT Title FROM GameData
    WHERE Category='...' LIMIT 5 ");
while ($row = mysql_fetch_array($results, MYSQL_ASSOC)) {
    // work with $row['Title']
}

answered Feb 26, 2011 at 17:21

Pascal MARTIN

402k82 gold badges665 silver badges666 bronze badges

Comments

Deep Kapadia · Accepted Answer · 2011-02-26 17:22:58Z

1

mysql_fetch_array only returns one row, you would have to loop through the rows

see example at: http://php.net/manual/en/function.mysql-fetch-array.php

answered Feb 26, 2011 at 17:22

Deep Kapadia

1,4589 silver badges22 bronze badges

Comments

mailo · Accepted Answer · 2011-02-26 17:25:37Z

1

You should use following query in order to make things right.

 SELECT `Title` FROM GameData 
 LEFT JOIN
 Category ON Category.Id = GameData.Category
 WHERE Category.Title = "$title" LIMIT 5

I assumed that Category has column Id.
I advise you to learn about JOINS.
Additionally, you may want to rename Category to Category_Id, and drop letter-case so Category would become category_id.

answered Feb 26, 2011 at 17:25

mailo

2,61122 silver badges19 bronze badges

Collectives™ on Stack Overflow

Select multiple values from the same row?

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related