Insert PHP variable in to MySQL database?

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.

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

PHP Code:

---

var_dump($userid); 


---

Quote:

---

Originally Posted by Mindzai

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

PHP Code:

---

var_dump($userid); 


---

---

Hi again,

Many thanks for your reply!

It returns the following:

65NULL

Quote:

---

Originally Posted by Mindzai

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

PHP Code:

---

var_dump($userid); 


---

---

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

Thanks alot!

Sam

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

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.

Quote:

---

Originally Posted by 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.

---

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

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

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

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.

Quote:

---

Originally Posted by Teufel

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.

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.

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!

Quote:

---

Originally Posted by Mindzai

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

---

Or not... :p

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:

Quote:

---

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.

Quote:

---

Originally Posted by Mindzai

Or not... :p

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?

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

PHP Code:

---

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

$userid = 66;
saveItem($userid); 


---

This may also help, especially the part about parameters.