SQL row results based on var

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
plankguy
Forum Newbie
Posts: 18
Joined: Sat Jun 25, 2005 7:39 pm

SQL row results based on var

Post by plankguy »

So i'm trying to query my sql table based on the variable that i've past from another page. ie: the user selects an option from a menu, then is sent to a template page with a variable '$page = $_GET' and based on the variable I need to get the info relating to the variable from my database.

So if the variable was '4', I need to get all the values of columns from row 4. I jsut have no idea where to start.

Code: Select all

<?php
$page = $_GET['page']; // var is 4

$query = "SELECT `index`, `name`, `description`, `genre` FROM `table`";
$result = mysql_query ($query);

$row =  mysql_fetch_row($result);
echo $row["name"];
echo $row["description"];
echo $row["genre"];
?>
But of course I get the first row, where I want row 4.

Help.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Code: Select all

<?php
$page = (int)@$_GET['page'];

$query = "SELECT
		`index`, `name`, `description`, `genre`
	FROM
		`table`
	LIMIT
		$page,1
	";
$result = mysql_query ($query) or die(mysql_error());
if ( $row =  mysql_fetch_row($result) ) {
	echo $row["name"], ' ',
		$row["description"], ' ',
		$row["genre"];
}
else {
	echo '...';
}
?>
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Post by printf »

You need to add the WHERE to the query so you can limit the result based on the question you will ask in the WHERE part of your statement!

Code: Select all

<?

if ( isset ( $_GET['page'] ) )
{
	$r = mysql_query ( "SELECT index, name, description, genre FROM table WHERE some_column = " . intval ( $_GET['page'] ) ) or die ( 'Query Error: ' . mysql_error () );

	if ( mysql_num_rows ( $r ) > 0 )
	{
		$data = mysql_fetch_assoc ( $r );

		echo $data['name'] . "<br />";
		echo $data['description'] . "<br />";
		echo $data['genre'] . "<br />";
	}
	else
	{
		echo 'query returned no results';
	}
}
else
{
	echo 'data expected is missing, action canceled';
}

?>

me!
plankguy
Forum Newbie
Posts: 18
Joined: Sat Jun 25, 2005 7:39 pm

Post by plankguy »

THanks for the help, but i'm getting an error:
Query Error: You have an error in your SQL syntax near 'index, name, description, genre FROM franchise_listing WHERE index = 0' at line 1

The $page var should be equal (=) to 'index' column, and it should be displaying that info. So I have:

Code: Select all

<?php
$r = mysql_query ( "SELECT index, name, description, genre FROM table_name WHERE index = " . intval ( $_GET['page'] ) ) or die ( 'Query Error: ' . mysql_error () );
?>
Doesn't work....any ideas? (BTW the 'index' column just contains numbers [0,1,2,3,4,5,etc...])

thnx
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

I think you might try putting index in backticks (`index`) because it is a keyword. The index column is often named `id`.
(#10850)
Rovas
Forum Contributor
Posts: 272
Joined: Mon Aug 21, 2006 7:09 am
Location: Romania

Post by Rovas »

Check with mysql that the query is good (it' s a matter of the fact of putting the name of the table).
Put

Code: Select all

WHERE table.index=" .S_GET["index"] ." ORDER BY name"
.
You don' t need

Code: Select all

intval

because the value will be numeric but you should check the index as security measure if it is numeric before quering the table
miro_igov
Forum Contributor
Posts: 485
Joined: Fri Mar 31, 2006 5:06 am
Location: Bulgaria

Post by miro_igov »

Rovas wrote:Check with mysql that the query is good (it' s a matter of the fact of putting the name of the table).
Put

Code: Select all

WHERE table.index=" .S_GET["index"] ." ORDER BY name"
.
You don' t need

Code: Select all

intval

because the value will be numeric but you should check the index as security measure if it is numeric before quering the table
This would be bad practice if you don't check the $_GET['index'] content, because someone could inject another sql query or just make the query not working :)
Post Reply