SQL errors and PHP

[fguy64]

OK, I am trying to get a handle on what kind of mySQL errors will feed info back to php. I know connection errors feed back to php and back to browsers, but what about the following...

{
    mysql_query( "INSERT INTO users VALUES ("
                +"'" +$id +"',"
                +"'" +$pwd +"',"
                +"'" +$email +"')" );

    header( "Location: menu.htm" );
}

the code block executes because I get redirected to menu.htm. But my users table doesn't get updated. I tried just removing the header() function, but then I get a blank page. I'ne looked the INSERT statement over pretty closely, I don't see anything wrong, but I'm just a rookie.

So the question is what can I make out of the fact that no error information gets returned to the browser, yet clearly the mysql_query is not working.

thanks.

 

[xav0989]

try a try-catch block:

function exception_error_handler($errno, $errstr, $errfile, $errline ) {
throw new ErrorException($errstr, 0, $errno, $errfile, $errline);
}
set_error_handler("exception_error_handler");

try {
     mysql_query( "INSERT INTO users VALUES ("
                +"'" +$id +"',"
                +"'" +$pwd +"',"
                +"'" +$email +"')" );
} catch (Exception $e) {
    exit('Caught exception: ',  $e->getMessage());
}

header( "Location: menu.htm" );

The code provided above is not meant to be on a production server. If you intend to use it on a production server, use this instead:

function exception_error_handler($errno, $errstr, $errfile, $errline ) {
throw new ErrorException($errstr, 0, $errno, $errfile, $errline);
}
set_error_handler("exception_error_handler");

try {
     mysql_query( "INSERT INTO users VALUES ("
                +"'" +$id +"',"
                +"'" +$pwd +"',"
                +"'" +$email +"')" );
} catch (Exception $e) {
    //write more advance data to an error log.
    exit('There was an unexpected error.');
}

header( "Location: menu.htm" );

IMPORTANT: Using this method (exceptions) will force you to use try-catch blocks to check for errors instead of the regular way.

 

[gomarc]

You can also try this.

First make sure your table “users” has only 3 fields. If your table has more than 3 fields, you need to specify where to insert the data.

Then you can use:

mysql_query( "INSERT INTO users VALUES (
                '$id',
                '$pwd',
                '$email')" );

 

[fguy64]

You can also try this.

First make sure your table “users” has only 3 fields. If your table has more than 3 fields, you need to specify where to insert the data.

Then you can use:

mysql_query( "INSERT INTO users VALUES (
                '$id',
                '$pwd',
                '$email')" );

ok goMarc I'll try, but...

Based on the following reference, how would mySQL know that what is within the single quotes is variable names, and not just text data?

http://www.w3schools.com/php/php_mysql_insert.asp

p.s. thanks xav0989, I hadn'tthought about try catch.

 

[gomarc]

At the end of the page you are making reference, check how it handles the PHP $_POST variables.

$sql="INSERT INTO Persons (FirstName, LastName, Age)
VALUES
('$_POST[firstname]','$_POST[lastname]','$_POST[age]')";

 

[fguy64]

ok gomarc, when I can I'll put all this stuff through the paces, and see how it all shakes out.

It's still not clear to me how....

mysql_query( "INSERT INTO users VALUES ( 'Smith', 'xyz', 'smith@aol.com')" );

will insert the stuff as text data, but your suggestion below will insert the contents of the variables. I guess even within the quotes, $ is a special character, which presumably would have to be escaped if I wanted the VARCHAR data to include $


mysql_query( "INSERT INTO users VALUES (
'$id',
'$pwd',
'$email')" );

Anyways, with time I'm sure it'll become clear. Thanks for the help.

 

[misson]

Based on the following reference, how would mySQL know that what is within the single quotes is variable names, and not just text data?

Within double quoted strings (and heredocs), variables are interpolated. Single quotes within double quotes are not special, they're just characters.

For collecting more information about failures, don't forget about driver specific error functions (eg mysql_error(), mysqli_error()). You can use error_log() to log extended error messages for your eyes only. Also remember that many SQL query functions (including mysql_query and mysqli_query) return FALSE if the query failed.

 

[fguy64]

Within double quoted strings (and heredocs), variables are interpolated. Single quotes within double quotes are not special, they're just characters.

...

That may or may not be true, but I doesn't really answer the question.

Within the double quotes is a mySQL statement.
within that mySQL statement is the sequence VALUES('Tom', 'Jones')
within the single quotes is text data.

now, if I were to replace Tom and Jones with $first and $last e.g.

VALUES('$first', '$last') and expect $first and $last to be treated as something other than text data, i.e. something other than a string of characters beginning with $, then to say single quotes within double quotes are just characters is not really illuminating.

Let me put this a different way. If the sequence VALUES('$first', '$last') actually results in two VARCHAR variables containing Tom and Jones, then how would i write that sequence so that my VARCHAR variables contain $first and $last as text strings themselves?

 

[gomarc]

...

If the sequence VALUES('$first', '$last') actually results in two VARCHAR variables containing Tom and Jones, then how would i write that sequence so that my VARCHAR variables contain $first and $last as text strings themselves?

$first  = '$first';
$last  = '$last';

 

[fguy64]

ok then it would seem to follow that php is somewhat different than other languages, at least the ones I am familiar with, in the way that it treats string literals and string variables.

I appreciate the time taken. I think I need to take two steps back an go back to school on my php basics. And then put all this stuff to the test. I'll return to this thread later.

all the best, and thanks again.

 

[misson]

That may or may not be true, but I doesn't really answer the question.
[...]
VALUES('$first', '$last') and expect $first and $last to be treated as something other than text data, i.e. something other than a string of characters beginning with $, then to say single quotes within double quotes are just characters is not really illuminating.

The important part of my answer is: "Within double quoted strings (and heredocs), variables are interpolated." The statement about single quotes is just an aside, noting that single quotes within double quotes do not function as single quotes outside of strings (i.e. single quotes do not prevent variable interpolation).

At this point, it's not entirely clear what you're asking about. Are you asking about PHP or MySQL's handling of the string? Part of my difficulty is I'm not sure what your conceptual framework is. As you post more, the clearer it will become.

Within the double quotes is a mySQL statement.
within that mySQL statement is the sequence VALUES('Tom', 'Jones')
within the single quotes is text data.

Not exactly. Within the PHP script is a double quoted string of no particular meaning. If it contains references to variables, they are replaced by the contents of the variables (after conversion to strings, if necessary). The resulting string is passed to the function mysql_query, which sends the string to MySQL. MySQL parses it as an INSERT, and the expressions "'Tom'" and "'Jones'" are parsed as strings (the varchar type is used for tables and procedures, not expressions). PHP contains no concept of SQL, and variables and strings in SQL are completely separate from PHP variables and strings.