[RESOLVED] Basic SQL issue

riddellchris

Hi,

I'm trying to learn this from a book and still it gives me errors, I don't know enough to be able to solve this so can you tell me please what's wrong.

The offending code is

$query =  "SELECT * FROM movie " .
"WHERE movie_id ='" . $_GET['movie_id'] . "'";

To me it just looks like a mess, not sure if it should or not but that's a direct copy!

Cheers

bpat1434

The query itself is fine.

The mysql_query call should look something like this:

$result = mysql_query($query) or die(mysql_error());

What's that result?

And what is the error you get?

nemonoman

Actually the code way it's set up is confusing.
Let's simplify:

If you put "" around a variable, then the value of the variable is substituted in the string.

$myname='my name is Nemo ';
echo "Hello $myname";
//echoes "Heloo my name is Nemo";

You can't do this with named array elements like $_GET['movie_id'] -- but the simple work around is "

$movieId=$_GET['movie_id'] ;

Second all those "." type constructs are just concatenating the quoted strings. You could pretty much get rid of them.

So now the query works out to:

$movieId=$_GET['movie_id'] ;

$query="SELECT * FROM movie WHERE movie='$movieId'";

bpat1434

I'm sorry, but you're mistaken. You can do that with named elements:

$me['name'] = 'Brett';

echo "Hello!  My name is {$me['name']}";

// OR

echo "Hello! My name is $me[name]";

/* OUTPUT:
Hello! My name is Brett

Hello! My name is Brett
*/

See: Array Manual Page

Either way, the query is symantically correct. The only issue is if they're not catching the error output (of mysql_error()). If they assume that a result will be returned, there's the flawed logic. Since the code doesn't show us that, I'm assuming they're not using an "or die(mysql_error())" statement with the mysql_query() call.

paulnaj

For what it's worth I always go for readability ...

<?php 
$query =  "
SELECT * 
FROM movie 
WHERE movie_id = '".$_GET['movie_id']."'
";
?>

PS
IMHO, always cut in and out of strings and always use single-quotes for strings. That is,

<?php 
$name = 'Fred';
$message = 'Hello '.$name.', how are you?';
?>

... is 'better' (couldn't think of the right word) than ...

<?php 
$name = 'Fred';
$message = "Hello $name, how are you?";
?>

... on grounds of readability again. No?

Easier to debug, infintesimally faster and best of all, using single-quotes all the time means you can save your double-quotes for HTML attribute values without having to escape them.

bpat1434

Easier to debug, infintesimally faster and best of all, using single-quotes all the time means you can save your double-quotes for HTML attribute values without having to escape them.

Not to mention saves you from erroneous syntax errors.

e.g.

echo "$1.50 per unit"; // errors, illegal variable name
echo '$1.50 per unit'; // echo's what it shows...

paulnaj

Hey bpat,

Can we have a sticky on the perils of double-quotes?

Paul.

Roger_Ramjet

Well I've always been in the single quote camp - for all the reasons mentioned.

Now to return to the original question:-

The first thing to do is to echo the query string to see what is actually being sent to the db. As often as not the problem is that $_GET['movie_id'] has no value, and you'll see this as soon as you echo the query string.

I always extend brett's construct to do this in the case of an error

$result = mysql_query($query) or die($query . '<br />' . mysql_error());

see the query and the error.

Now this only works when there is an error. In many cases though it is just that the query is valid but returns no results because eg the id does not exist, of the get var has not been set.

bpat1434

@: If a topic were to be created which intelligently discussed the idiosyncrasies of quotes, then I guess one would be obliged to "stick" it 😉

I usually like to do something like the following:

<?php

$query = "SELECT * FROM `table` WHERE `some_id`=" . (int)$_GET['item'];

$result = mysql_query($query);

if(!is_resource($result)) // If the query didn't execute properly....
{
    exit('<h4>Error [# ' . mysql_errno() . ']:</h4><p>' . mysql_error() . '</p><p style="white-space:pre;">' . $query . '</p>');
}

if(mysql_num_rows($result) < 1)
{
    exit('<h4>Sorry</h4><p>The query returned an empty result set.</p>');
}

// Handle all results now....
?>

Works very well when debugging scripts 😉

riddellchris

Wow, Thanks for all of your responses. I'm sorry that I didn't put in the original error but it is

Notice: Undefined index: movie_id in C:\apache\htdocs\movie_review_website\~scp.php on line 48

and seeing as (i think now) that the query as confusing as it may be is actually correct then this may mean there is another problem so I'm sorry if I have lead you down the wrong track. Line 48 is the line

					"WHERE movie_id ='" . $_GET['movie_id'] . "'";

Incase it is actually another issue here is the rest of my code.

/* Function to calculate if a movie made a profit loss or broke even */
function calculate_diffecences($takings, $cost) {
	$difference = $takings - $cost;

if ($difference < 0) {
		$difference = substr($difference, 1);
		$font_colour = 'red';
		$profit_or_loss = "$" / $difference . "m";
}	elseif ($difference > 0) {
	$font_colour = 'green';
	$profit_or_loss = "$" . $difference . "m";
} else {
	$font_colour = 'blue';
	$profit_or_loss = "Broke_even";
}
return "<font colour=\"$font_colour\">" . $profit_or_loss . "</font>";
}

// Funtion to get the director's name from the people table //
function get_director($director) {
	$query = "SELECT people_fullname " .
					 "FROM people " .
					 "WHERE people_id='$movie_director'";
	$results = mysql_query($query)
		or die(mysqlerror());
	$row = mysql_fetch_assoc($results);
	return $row['people_fullname'];
}

// Funtion to get the lead actor's name from the people table //
function get_leadactor($movie_leadactor) { 
    $query = "SELECT people_fullname " . 
               "FROM people " . 
               "WHERE people_id='$movie_leadactor'"; 
    $result = mysql_query($query) 
        or die (mysql_error()); 
    $row = mysql_fetch_assoc($result); 
    return $row['people_fullname']; 
}

$query =  "SELECT * FROM movie " .
					"WHERE movie_id ='" . $_GET['movie_id'] . "'";

$result = mysql_query($query, $link)
	or die(mysql_error());

Once again thankyou all for your help!!

hemantsaha

riddellchris wrote:
Hi,

I'm trying to learn this from a book and still it gives me errors, I don't know enough to be able to solve this so can you tell me please what's wrong.

The offending code is
$query =  "SELECT * FROM movie " .
"WHERE movie_id ='" . $_GET['movie_id'] . "'";
To me it just looks like a mess, not sure if it should or not but that's a direct copy!

Cheers

HI....try following.......

$query = "SELECT * FROM movie WHERE movie_id =$_GET[movie_id]";

I am sure will work fine.........

cheers

Roger_Ramjet

OK, that error message:

Notice: Undefined index: movie_id in C:\apache\htdocs\movie_review_website\~scp.php on line 48

means that the column 'movie_id' does not exist, or rather called something else. To help us out please run the following query and post the results here.

"DESCRIBE movie"

Describe will return the column specifications for the table movie so we can see what the id column is really called.

bpat1434

Roger, you're mixing the error codes up. Undefined index is used with arrays, so the $_GET['movie_id'] is not being set. If it were an issue with the mySQL table, it'd be "Unknown Column" 😉

@: Use the following code, as it should at least return something!!

$movie_id = (isset($_GET['movie_id']) && !empty($_GET['movie_id'])) ? $_GET['movie_id'] : 1;

$query = "SELECT * FROM movie WHERE movie_id=" . (int)$movie_id;
$result = mysql_query($query) or die(mysql_error());

Or, something like this:

$query = "SELECT * FROM movie";
if(isset($_GET['movie_id']) && !empty($_GET['movie_id']))
{
    $query .= " WHERE movie_id=" . (int)$_GET['movie_id'];
}
$result = mysql_query($query) or die(mysql_error());

Roger_Ramjet

Whoops, my bad :o