Fetch all rows from mysql to an array

This site is best viewed in a modern browser with JavaScript enabled.

svidotto

hi.

i'm pretty new to web programming and programming in general.

what i want is to fetch all the rows from a selected query in an array in wich every element ([0], [1], [2],...) is an associative array representing a row.
how can i do that?
is that a better method to extract rows from mysql.

here is what i have written yet but i't not working.

	try
	{
		$sql = "SELECT Data, Readed, Title AS Title FROM news, teams, users, prepared_news WHERE news.Template_news = prepared_news.id AND news.Id_team = teams.id AND teams.Id_user = users.id AND users.id = :Id ORDER BY Data DESC LIMIT(1,10)";
		$s = $connection->prepare($sql);


	$s->bindValue(':Id', $_SESSION['Currentuserid']);


	$s->execute();

		foreach ($s AS $row)
		{	
			$news_title_array[] = array(
				'Data_notizia' => $row['Data'],
				'Readed' => $row['Readed'],
				'Title' => $row['Title']
			);

		}	

}

gvre

You could use the PDO::FETCH_ASSOC fetch style.

Try putting the following after the $s->execute();

$result = $s->fetchAll(PDO::FETCH_ASSOC);

NogDog

gvre;1288745 wrote:
You could use the PDO::FETCH_ASSOC fetch style.

Try putting the following after the $s->execute();
$result = $s->fetchAll(PDO::FETCH_ASSOC);

This would generally be my recommendation, too, with the caveat that if a possibility exists that the query could return a very large number of rows (for some undefined value of "very large"), you could end up using a lot of memory in your PHP app, made worse by the fact that PHP arrays are not very memory-efficient -- but that's more a question of whether or not you want/need to create one result array, versus whether or not to use the above technique to do so.

svidotto

NogDog;1288785 wrote:
This would generally be my recommendation, too, with the caveat that if a possibility exists that the query could return a very large number of rows (for some undefined value of "very large"), you could end up using a lot of memory in your PHP app, made worse by the fact that PHP arrays are not very memory-efficient -- but that's more a question of whether or not you want/need to create one result array, versus whether or not to use the above technique to do so.

mysql will never return more than 10 rows because of the limit clause i think.
by the way:
my $sql is changed now and it is:

	$sql = "SELECT Data_notizia, Readed, :wich_title AS Title FROM system_news, teams, users, prepared_news WHERE system_news.Template_news = prepared_news.id AND system_news.Id_team = teams.id AND teams.Id_user = users.id AND users.id = :Id ORDER BY Data_notizia DESC LIMIT :start_with, 10";
	$s = $connection->prepare($sql);

	$s->bindValue(':wich_title', $_POST['type_news']);
	$s->bindValue(':Id', $_SESSION['Currentuserid']);
	$s->bindValue(':start_with', $_POST['from'], PDO::PARAM_INT);

	$s->execute();
		foreach ($s AS $row)
		{	
			$news_title_array[] = array(
				'Data_notizia' => $row['Data_notizia'],
				'Readed' => $row['Readed'],
				'Title' => $row['Title']
			);

the mysql send to me this error: Error1exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0', 10' at line 1' in C:\xampp\htdocs\volleyballmanager\login\extract_news_title.php:91

i think this is because the bindvalue function puts quotes araund :start_with.
how can i avoid that problem?

THX GUYS!

gvre

You must cast the 'from' value to int.

$s->bindValue(':start_with', (int)$_POST['from'], PDO::PARAM_INT);

ps. You could replace the foreach with the more efficient fetchAll(PDO::FETCH_ASSOC)

svidotto

gvre;1288905 wrote:
You must cast the 'from' value to int.
$s->bindValue(':start_with', (int)$_POST['from'], PDO::PARAM_INT);
ps. You could replace the foreach with the more efficient fetchAll(PDO::FETCH_ASSOC)

fantastic, now it works, i only have to discover how to "echo" the results back to my jquery post function and this is it.

how would the syntax be and what's PDO::FETCH_ASSOC do, why it's better than foreach?

sorry for so much questions!

NogDog

What you return depends on what your jQuery expects, but typically JSON objects are used, so you could do:

header('Content-Type: application/json');
echo json_encode($yourArrayHere);
exit;