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.
    Need help bringing your website to life? http://www.nickelleon.com

  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 06: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 06:58 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 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

"

"

X vBulletin 4.2.2 Debug Information

  • Page Generation 0.14821 seconds
  • Memory Usage 3,042KB
  • Queries Executed 15 (?)
More Information
Template Usage (37):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (4)bbcode_php
  • (7)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (15)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (1)navbar_moderation
  • (1)navbar_noticebit
  • (1)navbar_tabs
  • (2)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (1)pagenav_pagelink
  • (15)postbit
  • (15)postbit_onlinestatus
  • (15)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (26):
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/functions_navigation.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_facebook.php
  • ./includes/functions_calendar.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (75):
  • init_startup
  • friendlyurl_resolve_class
  • init_startup_session_setup_start
  • database_pre_fetch_array
  • database_post_fetch_array
  • init_startup_session_setup_complete
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • load_show_variables
  • load_forum_show_variables
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • strip_bbcode
  • friendlyurl_clean_fragment
  • friendlyurl_geturl
  • forumjump
  • cache_templates
  • cache_templates_process
  • template_register_var
  • template_render_output
  • fetch_template_start
  • fetch_template_complete
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • pagenav_page
  • pagenav_complete
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • build_navigation_data
  • build_navigation_array
  • check_navigation_permission
  • process_navigation_links_start
  • process_navigation_links_complete
  • set_navigation_menu_element
  • build_navigation_menudata
  • build_navigation_listdata
  • build_navigation_list
  • set_navigation_tab_main
  • set_navigation_tab_fallback
  • navigation_tab_complete
  • fb_like_button
  • showthread_complete
  • page_templates