PHP MySQL insert statement not working.

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

PHP MySQL insert statement not working.

WyattVW

ok here is the problem code
the output here is the error message i have programmed for when the query doesn't work, i know all of my fields are getting posted and collected by the variables but it doesn't insert the fields, i can't seem to track down the problem here.

if (isset($_POST['first_name'])
    && isset($_POST['last_Name'])
    && isset($_POST['user_email'])
    && isset($_POST['mailing_address'])
    && isset($_POST['user_name']) 
    && isset($_POST['user_pass']) 

) {
    $first_name = get_post('first_name');
    $last_Name = get_post('last_Name');
    $user_name = get_post('user_name');
    $user_pass = get_post('user_pass');
    $user_email = get_post('user_email');
    $mailing_address = get_post('mailing_address');

$query = "INSERT INTO users VALUES" . "('','$first_name', '$last_Name', '$user_email', '$mailing_address', '$user_name', '$user_pass')";

if (!mysql_query("INSERT INTO users VALUES" . "('','$first_name', '$last_Name', '$user_email', '$mailing_address', '$user_name', '$user_pass')", $db))
    echo "INSERT failed: $query<br />" . mysql_error() . "<br /><br />";
}

ginerjm

Hmmm.....

You assigned a query statement to a variable, but then you executed a call to MySQL_query with an inline query statement. Odd.

Anyway - what message did MySQL_error give you in that echo line?

BTW- You do know that MySQL_* is all deprecated and you should begin migrating to either mysqli or pdo for your db work.

WyattVW

i assigned the query to a variable to begin with but i read online that the mysql_query statement sometimes does not work when passing a variable rather than the inline statement, i changed it to see if that would fix my issue.

the error message i get is the message i created here without any specific SQL error appended to the end of it.

maybe replacing the deprecated functions will make this work i will try that now thanks.

ginerjm

I'm curious where you saw anything about an inline query working better than a variable. Crazy!

I don't understand your second sentence at all.

Turn on error checking. Add this:

	error_reporting(E_ALL | E_STRICT | E_NOTICE);
	ini_set('display_errors', '1');

Plus - you shouldn't use $query in your error statement when you didn't actually run that query. Change back to using the variable in your query.

WyattVW

ok i have never used error checking before where do i add this?

ginerjm

At the beginning of every script that you are still developing. You don't want to display errors once you go into production.

NogDog

If using the (deprecated) mysql PHP extension, you should sanitize all non-numeric inputs via mysql_real_escape_string().

$query = sprintf(
    "INSERT INTO users VALUES" . "('', '%s', '%s', '%s', '%s', '%s', '%s')"; 
    mysql_real_escape_string($first_name),
    mysql_real_escape_string($last_Name),
    mysql_real_escape_string($user_email),
    mysql_real_escape_string($mailing_address),
    mysql_real_escape_string($user_name),
    mysql_real_escape_string($user_pass)
);
if(mysql_query($query) == false) {
    throw new Exception(mysql_error().PHP_EOL.$query);
}

Also, my suspicion is that the first value should be NULL instead of '', assuming it's an auto-numbered primary key? (Actually, if that's the case, you could provide a list of columns that does not include that field, then you don't have to have it in your VALUES() clause; besides which using an explicit field list avoids nasty bugs when your database table is altered in any way that changes the default field order.

WyattVW

OK i have it now working, but its all using the now deprecated mysql* is there a tutorial or something anyone can share that will help with converting over to PDO? i tried using mysqli but for some reason my site was not allowed access to the site while using the improved version. giving me this message.

Database access failed: Access denied for user ''@'localhost' (using password: NO)

ive looked all over the internet for a fix to this but all i get is people that had a similar problem and couldn't find a fix.

WyattVW

can i get the same effect writing it like this?

require_once 'connect.php';
$result = mysql_query("SELECT * FROM users");
$rows = mysql_num_rows($result);
if (!$result) die ("Database access failed: " . mysql_error());


if (isset($_POST['first_name'])
    && isset($_POST['last_Name'])
    && isset($_POST['user_email'])
    && isset($_POST['mailing_address'])
    && isset($_POST['user_name']) 
    && isset($_POST['user_pass']) 
    && isset($_POST['user_pass_check'])
) {
    $first_name = mysql_real_escape_string(get_post('first_name'));
    $last_Name = mysql_real_escape_string(get_post('last_Name'));
    $user_name = mysql_real_escape_string(get_post('user_name'));
    $user_pass = mysql_real_escape_string(get_post('user_pass'));
    $user_pass_check = mysql_real_escape_string(get_post('user_pass_check'));
    $user_email = mysql_real_escape_string(get_post('user_email'));
    $mailing_address = mysql_real_escape_string(get_post('mailing_address'));
    $user_ID = $rows+1;

$query = "INSERT INTO users VALUES" . "('$user_ID','$first_name', '$last_Name', '$user_email', '$mailing_address', '$user_name', '$user_pass')";

if (!mysql_query($query, $db))
    echo "INSERT failed: $query<br />" . mysql_error() . "<br /><br />";
}

ginerjm

Does your host support the mysqli extension? You might have to use PDO, as I do since my host doesn't have mysqli.

As for your code. You wrote it too quickly to read what you wrote.

require_once 'connect.php'; 
$result = mysql_query("SELECT * FROM users"); 
$rows = mysql_num_rows($result); 
if (!$result) die ("Database access failed: " . mysql_error());

1 - Why are you doing a query for all records when the whole point is to do an insert? Are you actually using an id that represents the number of rows at a given time? No Good. What happens if you delete a row?
2 - You must check the results of the query before trying to use the results!! Your check for num rows will fail if $result is not valid because the query failed, so swap them.
I don't see the error checking code I provided.

WyattVW

Thanks I'm sure that will be immensely helpful.

NogDog

WyattVW;1318237 wrote:
OK i have it now working, but its all using the now deprecated mysql* is there a tutorial or something anyone can share that will help with converting over to PDO? i tried using mysqli but for some reason my site was not allowed access to the site while using the improved version. giving me this message.

Database access failed: Access denied for user ''@'localhost' (using password: NO)

ive looked all over the internet for a fix to this but all i get is people that had a similar problem and couldn't find a fix.

Here's something to chew on PDO-wise:

<?php
$host = 'localhost';
$dbName = 'my_database';
$user = 'secret';
$pass = 'topSecret';
$pdo = new PDO("mysql:host=$host;dbname=$dbName", $user, $pass);
$sql = "
INSERT INTO users (
	first_name,
	last_Name,
	user_email,
	mailing_address,
	user_name,
	user_pass
) VALUES (
	:first_name,
	:last_Name,
	:user_email,
	:mailing_address,
	:user_name,
	:user_pass
)
";
if(($stmt = $pdo->prepare($sql)) == false) {
	throw new Exception(print_r($pdo->errorInfo(),1).PHP_EOL.$sql);
}
if($stmt->execute(array(
	':first_name'      => $first_name,
	':last_Name'       => $last_Name,
	':user_email'      => $first_name,
	':mailing_address' => $user_email,
	':user_name'       => $user_name,
	':user_pass'       => $user_pass
)) == false) {
	throw new Exception(print_r($stmt->errorInfo(),1).PHP_EOL.$sql);
}

WyattVW

1 - Why are you doing a query for all records when the whole point is to do an insert? Are you actually using an id that represents the number of rows at a given time? No Good. What happens if you delete a row?
2 - You must check the results of the query before trying to use the results!! Your check for num rows will fail if $result is not valid because the query failed, so swap them.
I don't see the error checking code I provided.

the $result variable is for checking for the updated fields, they display further down on the page, i just declared it at the top so i did not have to write 2 variables to get the number of rows.

if this is a bad way of inserting User ID how do you insert a value into a row with an auto increment ID field?

ginerjm

You misunderstood what I was saying.

WyattVW

WyattVW;1318255 wrote:
if this is a bad way of inserting User ID how do you insert a value into a row with an auto increment ID field?

well i managed to get it auto incrementing the ID field on its own by changing my query statement to this.

"INSERT INTO users(first_name, last_Name, user_email, mailing_address, user_name, user_pass) VALUES" . "('$first_name', '$last_Name', '$user_email', '$mailing_address', '$user_name', '$user_pass')"

ginerjm

So now you understand what the term "auto-increment" means.

Yes - that is a much better query statement altho I don't know why you split it up at 'VALUES'.

BTW - while I am helping you accomplish a single task here, it would be lacking of me not to tell you that you have several other issues that you should handle. For one - DO NOT STORE A PASSWORD IN PLAIN TEXT!

WyattVW

about the password this database is on my workstation and its used only for testing, i will have the password field encrypted for the release version, thanks for all the help here.

xvszero

Yeah generally speaking you don't want to insert anything into an auto_increment field, the "auto" part of it will handle that itself.