www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Insert PHP variable in to MySQL database?

  1. #1
    Join Date
    Jul 2009
    Posts
    23

    Exclamation 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.

  2. #2
    Join Date
    Nov 2008
    Posts
    2,477
    What is the output of the following, placed directly before the mysql_query call:

    PHP Code:
    var_dump($userid); 

  3. #3
    Join Date
    Jul 2009
    Posts
    23

    Re:

    Quote Originally Posted by Mindzai View Post
    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

  4. #4
    Join Date
    Jul 2009
    Posts
    23

    Exclamation Re: php variable insert problem

    Quote Originally Posted by Mindzai View Post
    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

  5. #5
    Join Date
    Nov 2008
    Posts
    2,477
    Well there's your problem, but we cant tell you how it's happening without seeing your code.

  6. #6
    Join Date
    Jan 2008
    Posts
    66
    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.

  7. #7
    Join Date
    Jul 2009
    Posts
    23

    Exclamation

    Quote Originally Posted by nickelleon View Post
    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

  8. #8
    Join Date
    Apr 2008
    Posts
    157
    Is `userid` and int?
    Then you don't need the parentheses:

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

  9. #9
    Join Date
    Nov 2008
    Posts
    2,477
    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.

  10. #10
    Join Date
    Jul 2009
    Posts
    23

    Exclamation

    Quote Originally Posted by Teufel View Post
    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.

  11. #11
    Join Date
    Nov 2008
    Posts
    2,477
    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.

  12. #12
    Join Date
    Jul 2009
    Posts
    23

    Exclamation Bulk of the Code Extract

    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!
    Last edited by djstrictlylegit; 08-03-2009 at 07:39 PM. Reason: Adding in all of the code

  13. #13
    Join Date
    Nov 2008
    Posts
    2,477
    Quote Originally Posted by Mindzai View Post
    ...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.

  14. #14
    Join Date
    Jul 2009
    Posts
    23

    Exclamation

    Quote Originally Posted by Mindzai View Post
    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?

  15. #15
    Join Date
    Nov 2008
    Posts
    2,477
    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.
    Last edited by Mindzai; 08-03-2009 at 07:58 PM.

Thread Information

Users Browsing this Thread

There are currently 3 users browsing this thread. (0 members and 3 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles