PHP sql does not return all values

Question

When I do this query directly from PHP my admin I got 5 results.

SELECT img FROM `table`.`goog` WHERE `url` LIKE '%google.com%' and `pictureID` != 'picture_50d22657a423b6.62354164';

however, when my code

$sql2 = "SELECT img FROM `table`.`goog` WHERE `url` LIKE '%$url%' and `pictureID` != '$pictureID';";
    // echo $sql2;
$result2=mysql_query($sql2);
$rows2 = mysql_fetch_array($result2);

mysql_close($con);

print_r(count($rows2));

echo "<br/>";

echo $sql2;

it prints out

2 
SELECT img FROM `ashkan`.`goog` WHERE `url` LIKE '%66.228.42.45%' and `pictureID` != 'picture_50d22657a423b6.62354164';

I would also like to note that the query I run inside of php my admin is copied from what my code returns..

So, why does one returns only 2 items, and the other returns 5?

EDIT

I added the following to the bottom

var_dump($rows2);
echo "<br/>";
echo mysql_num_rows($result2);

and got this

array(2) { [0]=> string(1) "b" ["img"]=> string(1) "b" } 
5

I have something that manually selects a row

Essentially I want to be able to do

echo $rows2[somenum]; //that some number is at random and only one at a time

however, as is, I can only access item 0 and not all five.

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. — John Conde
– John Conde, Commented Dec 19, 2012 at 20:54

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

UPDATED CODE

Here's your updated code:

$mysqlQuery=mysql_query("SELECT img FROM `table`.`goog` WHERE `url` LIKE '%$url%' and `pictureID` != '$pictureID");
$databaseArray=array();

while($arrayQuery=mysql_fetch_array($mysqlQuery))
{
    $imageName=$arrayQuery['img'];
    
    $databaseArray[]=$imageName;
}

echo "Database returned ".count($databaseArray)." rows.";

print_r($databaseArray);

echo $databaseArray[0]; // prints image name row 1
echo $databaseArray[1]; // prints image name row 2
echo $databaseArray[2]; // prints image name row 3
// etc etc etc

Old code

Do this:

$sql2 = "SELECT img FROM `table`.`goog` WHERE `url` LIKE '%$url%' and `pictureID` != '$pictureID';";
    // echo $sql2;
$result2=mysql_query($sql2);

while($data = mysql_fetch_array($result2))
{
    var_dump($data);
}

mysql_fetch_array only returns one row at a time. The code above loops all the rows find in your database. If you only want to count the rows, then check mysql_num_rows() function.

You should also use mysqli_* and not mysql_*, because it's deprecated. Please read more at: http://php.net/manual/en/book.mysqli.php

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 19, 2012 at 20:55

Robinjoeh

3453 silver badges12 bronze badges

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

2 Comments

Cripto Over a year ago

i added some updates above. How can I achieve what I added after I edited it.

Robinjoeh Over a year ago

You don't have to. It's automatically closed at the end of the page load. Else you could always close it at the bottom of the page, if you want. But it's not needed as I said. Also, don't forget to set the correct answer.

Community · Accepted Answer · 2023-11-21 15:10:29Z

1

mysql_fetch_array() only fetches one row from result set and moves the pointer ahead one.

If you want a count of the number of rows a query returns use mysql_num_rows()

echo mysql_num_rows($result2);

edited Nov 21, 2023 at 15:10

CommunityBot

11 silver badge

answered Dec 19, 2012 at 20:54

John Conde

220k100 gold badges464 silver badges504 bronze badges

Comments

PhearOfRayne · Accepted Answer · 2012-12-19 20:57:42Z

1

By using:

print_r(count($rows2));

Your just counting the number of array keys for this row

You should be doing this instead:

echo mysql_num_rows($result2);

answered Dec 19, 2012 at 20:57

PhearOfRayne

5,0504 gold badges33 silver badges44 bronze badges

Comments

Alex · Accepted Answer · 2012-12-19 20:55:17Z

0

You have to iterate over the results.

$sql2 = "SELECT img FROM `table`.`goog` WHERE `url` LIKE '%$url%' and `pictureID` != '$pictureID';";
    // echo $sql2;
$result2=mysql_query($sql2);

// iterate over the results
while($row = mysql_fetch_assoc($result2)) {
    print_r($row); 
}

mysql_close($con);

answered Dec 19, 2012 at 20:55

Alex

12.5k7 gold badges47 silver badges52 bronze badges

Collectives™ on Stack Overflow

PHP sql does not return all values

4 Answers 4

UPDATED CODE

Old code

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

UPDATED CODE

Old code

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related