Insert PHP variable in to MySQL database?

[djstrictlylegit]

Hello all,

I have an integer stored in a variable named $userid and am trying to insert it in to a MySQL database.

I have been trying to use the following query:

$query = "INSERT INTO rota (userid, person) VALUES ('$userid','".db_escape($_POST['person'])."')";

Now, the query executes with no problem and the person value is correctly stored in the database. However, the userid variable inserts as '0' even though the value is actually '65'.

I have been trying everything I can for hours and no matter what I do the query always inserts '0' instead of the actual value of $userid which is '65'.


Any help at all would be HUGELY appreciated!


Thank you in advance.

[Mindzai]

What is the output of the following, placed directly before the mysql_query call:

var_dump($userid); 

[djstrictlylegit]

What is the output of the following, placed directly before the mysql_query call:

var_dump($userid); 

Hi again,

Many thanks for your reply!

It returns the following:

65NULL

[djstrictlylegit]

What is the output of the following, placed directly before the mysql_query call:

var_dump($userid); 

sorry, I meant to say var_dump returns NULL Any ideas?

Thanks alot!

Sam

[Mindzai]

Well there's your problem, but we cant tell you how it's happening without seeing your code.

[nickelleon]

If its returning NULL, then the value of $userid is NULL. Take a look at your PHP to figure out why or post more here for our assistance.

[djstrictlylegit]

If its returning NULL, then the value of $userid is NULL. Take a look at your PHP to figure out why or post more here for our assistance.

Thanks to all of you for your replies.

Before the query, I gather the value of the user id, when I echo it, the correct value of 65 is returned. This code is as follows:

$useridquery = "SELECT userid FROM user WHERE email = '$email'";
$result = mysql_query($useridquery);
while ($row = mysql_fetch_assoc($result)) {
$userid = $row["userid"];
echo "$userid";
}

Then for some reason when I try and insert this value in to the database, the value that is inserted is '0' despite the fact that I'm trying to insert the $userid variable which correctly echo's the value 65.

The SQL query is:

$query = "INSERT INTO rota (userid, person) VALUES ('$userid','".db_escape($_POST['person'])."')";

The POST value 'person' is inserted correctly (from the users input), however as I say the userid value is inserted as '0'.


If anyone could help me I would really appreciate it. Let me know if you require the full code to be posted.

Thanks,

Sam

[Teufel]

Is `userid` and int?
Then you don't need the parentheses:

$query = "INSERT INTO rota (userid, person) VALUES (".$userid.", '".db_escape($_POST['person'])."')";

[Mindzai]

You say it echoes the correct result, however as the var_dump showed, by the time you come to use the variable it has a value of null. The 0 is probably the field's default value. Please post your full code, and please use the [php] tags so it is properly formatted. Also make sure you have error_reporting enabled and set to E_ALL.

[djstrictlylegit]

Is `userid` and int?
Then you don't need the parentheses:

$query = "INSERT INTO rota (userid, person) VALUES (".$userid.", '".db_escape($_POST['person'])."')";

userid is indeed an int.

I tried your suggestion '$query = "INSERT INTO rota (userid, person) VALUES (".$userid.", '".db_escape($_POST['person'])."')";'

but it returned an SQL error:

An Error Occurred: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use


I dont understand why when I echo out the variable it shows the correct value 65 but when I try to insert the variable in to the database it inserts 0.

[Mindzai]

There is nothing wrong with your original SQL. Quotes are optional for integers. Like I said before, the issue is that the value of your variable is not being maintained. However, we can't help you without seeing the code.

[djstrictlylegit]

Okay, here is the full code:

<?php
include("scripts/dbconnection.php"); //start database connection

//checks cookies to make sure they are logged in
if(isset($_COOKIE['ID_my_site']))
{
$email = $_COOKIE['ID_my_site'];
$pass = $_COOKIE['Key_my_site'];
$query = "SELECT forename, surname FROM user WHERE email = '$email'";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());
$check = mysql_query("SELECT * FROM user WHERE email = '$email'")or die(mysql_error());
while($info = mysql_fetch_array( $check ))
{

//if the cookie has the wrong password, they are taken to the login page
if ($pass != $info['password'])
{ header("Location: login.php");
}

//otherwise they are shown the admin area
else
{
}
}
}
else

//if the cookie does not exist, they are taken to the login screen
{
header("Location: login.php");
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Cleaning Rota - Options</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta http-equiv="Content-Style-Type" content="text/css" />
<link href="css/layout.css" rel="stylesheet" type="text/css" />
</head>
<body>
<noscript><p>** Scripts have been disabled in your web browser, as a result of this some features may be unavailable. **</p></noscript>
<div id="rotamenu"> <br />
<?php
$useridquery = "SELECT userid FROM user WHERE email = '$email'";
$result = mysql_query($useridquery);
while ($row = mysql_fetch_assoc($result)) {
$userid = $row["userid"];
echo "$userid";
}



switch($_GET['action']){
case 'delete':
$title = 'Delete a Person';
$content = DeleteItem();
break;
case 'new':
$title = 'Add a Person';
$content = ManageItem();
break;
case 'save':
$title = 'Save a Person';
$content = SaveItem();
break;
default:
$title = 'Options';
$content = ShowList();
break;
}


function ShowList(){
// we are showing the table, no initial actions are needed
// so we'll jump straight into the table

$output = '<table border="1" width="620" cellpadding="9" style="border-collapse: collapse">
<tr>
<th>Person</th>
<th width="76">Action</th>
</tr>';

// run the query, we're putting the things that aren't purchased at the top
$query = mysql_query("select * from rota where userid='" . $userid . "' order by person asc");

// loop all the records
while($row = mysql_fetch_assoc($query)){
$output .= '<tr>';
$output .= '<td>' . $row['person'] . '</td>';
$output .= '<td width="76"><a href="?action=edit&personid=' . $row['personid'] . '"> ';

// need to add slashes as we're dealing with javascript here
$output .= '<a href="javascript:checkDelete(\'' . addslashes($row['person']) . '\',' . $row['personid'] . ');">Delete</a></td>';
$output .= '</tr>';
}
$output .= '</table>';

return $output;
}

function DeleteItem(){

// in the query we convert it to an integer to prevent any injection
if(mysql_query("delete from rota where personid='".(int)$_GET['personid']."'")){
$output = '<b>One member has been removed from the cleaning rota.</b><br/><br/>';
}else{
$output = '<b>An Error Occurred: ' . mysql_error() . '</b><br><br>';
}
// show the list
$output .= ShowList();

return $output;
}

function ManageItem(){
if(isset($_GET['personid'])){
// if we're editing we need to grab the stuff from the database

// convert to integer (if its not a number it'll become zero
$personid= (int)$_GET['personid'];

$query = mysql_query("select * from rota where personid='" . $personid . "' limit 1");
$row = mysql_fetch_assoc($query);
}else{
// set up blank array
$row['personid'] = '';
$row['person'] = '';
}

// we have lots of HTML here, so we're breaking out of PHP, but we need to stop it outputting
// so we'll use output buffering and capture the result

ob_start();
?>
<form method="POST" action="<?=$_SEVER['PHP_SELF']?>?action=save">
<?php

if(isset($_GET['personid']) && $personid > 0){
echo '<input type="hidden" name="personid" value="' . $personid . '">';
}

?>
<table border="0" width="450">
<tr>
<td><font size="2">Person:</font></td>
<td><input type="text" name="person" size="20" value="<?php echo htmlspecialchars($row['person']); ?>"></td>
</tr>
<tr>
<td colspan="2">
<p align="center">
<input type="submit" value="Add Person" name="submit"></td>
</tr>
</table>
</form>
<?php
// get output buffer and then clean it up
$output = ob_get_contents();
ob_end_clean();

return $output;
}

function SaveItem(){

if(isset($_POST['personid'])){
// we are updating
// using our custom db escape function
$query = 'update `rota` set ';
$query .= " `person`='".db_escape($_POST['person'])."', ";
$query .= " where personid='".(int)$_POST['personid']."' limit 1";

if(mysql_query($query)){
$output = '<b>Your person has been updated.</b><br/><br/>';
}else{
$output = '<b>An Error Occurred: ' . mysql_error() . '</b><br><br>';
}

}else{
// we are adding

// we are updating
// using our custom db escape function
$query = "INSERT INTO rota (userid, person) VALUES ('$userid','".db_escape($_POST['person'])."')";

if(mysql_query($query)){
$output = '<b>One person has been added to the cleaning rota.</b><br/><br/>';
}else{
$output = '<b>An Error Occurred: ' . mysql_error() . '</b><br><br>';
}
}
// show the list
$output .= ShowList();

return $output;

}

function db_escape($string){
if(function_exists('mysql_real_escape_string')){
return mysql_real_escape_string($string);
}else{
return mysql_escape_string($string);
}
}
?>


HUGE kudos to anyone that can offer any help to this peculiar problem.

Thanks!

[Mindzai]

...and please use the [php ] tags so it is properly formatted.

Or not...

Your issue is that you are trying to use $userid inside a function. Functions have their own scope, and so $userid has no value within your function because it is assigned a value in global scope. You will need to pass in the $userid as a parameter (don't use the global keyword, which will also work but is bad).

Incidentally if you'd done this:

Also make sure you have error_reporting enabled and set to E_ALL.

PHP would have given you a friendly notice telling you about this problem. It's always best to develop with all errors and warnings displayed.

[djstrictlylegit]

Or not...

Your issue is that you are trying to use $userid inside a function. Functions have their own scope, and so $userid has no value within your function because it is assigned a value in global scope. You will need to pass in the $userid as a parameter (don't use the global keyword, which will also work but is bad).

Incidentally if you'd done this:



PHP would have given you a friendly notice telling you about this problem. It's always best to develop with all errors and warnings displayed.

First off apologies for not enabling error reporting, I posted before I read your post, next time I will use the quote tags to properly format the code.

Thank's for helping out, do you know how I would go about passing $userid as a parameter?

[Mindzai]

Just pass in the userid when you call the function. For example:

function saveItem($userid) {
    echo "User ID: $userid";
}

$userid = 66;
saveItem($userid); 

This may also help, especially the part about parameters.