Why does my php returns one value (one row) from mysql?

Question

I have a php file that should returns value from mysql, her is my php file:

<?php

$host = "localhost"; // Host name 
$username = "UserName"; // Mysql username 
$password = "PassWord"; // Mysql password 
$db_name = "DbName"; // Database name 
$tbl_name = "Notes"; // Table name 

// Connect to server and select databse.
mysql_connect($host, $username, $password)or die("cannot connect"); 
mysql_select_db($db_name)or die("cannot select DB");




// To protect MySQL injection (more detail about MySQL injection)
$mytitle = $_REQUEST['title'];
$mytitle = stripslashes($mytitle);
$mytitle = mysql_real_escape_string($mytitle);

$sql="SELECT * FROM $tbl_name WHERE title = '$mytitle'";


$result = mysql_query($sql);

// Mysql_num_row is counting table row
$count = mysql_num_rows($result);

$count = 0;
$items;

while($row = mysql_fetch_array($result)) {
    $item['userName'] = $row['userName'];
    $item['title'] = $row['title'];
    $item['comments'] = $row['comments'];
    $item['commentsTime'] = $row['commentsTime'];
    $item['commentsDate'] = $row['commentsDate'];
    $items[$count] = $item;
}

echo json_encode($items);

?>

The file does return only one value when I run the code from my browser, I access the file via URL (http://MyWeb.com/ReadNotesByTitle.php?title=titleName).

Can any one tell me what is the wrong with my code? Thanks

Farshad · Accepted Answer · 2014-08-24 04:31:08Z

1

You have missed $count++; inside while loop

answered Aug 24, 2014 at 4:31

Farshad

1,4951 gold badge9 silver badges12 bronze badges

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

Comments

cdhowie · Accepted Answer · 2014-08-24 04:31:57Z

1

You never increment $count; it always has the value 0. So the second item will overwrite the first, the third will overwrite the second, and so on. Ultimately, you will only return the last row fetched from the database.

Increment $count at the end of the loop to solve the problem:

    $items[$count] = $item;
    $count++;
}

Alternatively, you can append an element to an array without keeping track of the count. Remove the $count variable entirely and change your array assignment to:

$items[] = $item;

answered Aug 24, 2014 at 4:31

cdhowie

172k25 gold badges303 silver badges324 bronze badges

Comments

ksealey · Accepted Answer · 2014-08-24 04:37:28Z

0

While I see this has already been answered I thought I should note you are making your code more complicated than it has to be. You're entire while loop can be summarized as below and will give your desired results

while($row = mysql_fetch_array($result)) {
    $items[] = $row;
}

answered Aug 24, 2014 at 4:37

ksealey

1,7381 gold badge17 silver badges16 bronze badges

Collectives™ on Stack Overflow

Why does my php returns one value (one row) from mysql?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related