variable not inserting correctly in mysql from PHP

Question

Here is my PHP code:

 // Collect data from URL 
$mid = $_GET['m'];

if (isset($_POST['submit'])) 
{ 
$insert = "insert into table SET from_id = '".$loginuser['members_id']."', to_id = '". $mid ."', date = '".$_POST['date']."' ";
    $add_member = mysql_query($insert);     
}

The data gets entered in the database correctly except the $mid

But if in my HTML I put this :

    <?php print $mid;?>

Then i can see the print of the ID number ... so I know my variable $mid has the proper value.... I don't know why it not getting inserted in the DB.

I also tried this SQL

$insert = "insert into table SET from_id = '".$loginuser['members_id']."', to_id = "$mid", date = '".$_POST['date']."' ";
    $add_member = mysql_query($insert);

Same thing... everything works except the value of $mid doesn't go in the DB.

My field in the DB is set to Int(11) and there is no mistake in the column name.. i checked 5 times... Don'T know what's wrong.. thx

ENTIRE CODE HERE :

<?
ob_start();
include 'datalogin.php';
//checks cookies to make sure they are logged in 
    if(isset($_COOKIE["user"]))
    {
    $username = $_COOKIE["user"]; 
    $pass = $_COOKIE["password"];
    $check = mysql_query("SELECT * FROM members WHERE email = '$username'")or   die(mysql_error()); 
    $loginuser = false;
        while($info = mysql_fetch_array( $check ))
        {   
            if(! $loginuser)
                 { $loginuser = $info; }

            //if the cookie is present but has the wrong password, they are taken to the login page 
            if ($pass != $info['password']) 
            {
                header("Location: login.php"); 
                exit();
            }  
            else //if the cookie is present and doesn'T have the wrong password they are shown the admin area    
            { 
                include 'header.php';
            }
        }
    }

    else //if there is no cookie present
            { 
                header("Location: login.php"); 
                exit();
            }

 // Collects data from images table 
$mid = $_GET['m'];
 $data = mysql_query("SELECT images.image_id, images.members_id, images.image_url, members.members_id, members.name, members.age
FROM members
LEFT JOIN images
ON members.members_id=images.members_id  WHERE members.members_id ='". $mid ."' ") 
 or die(mysql_error()); 
$data2 = mysql_fetch_array( $data );



    if (isset($_POST['submit'])) 
    { 


        $insert = "insert into booking SET from_id = '".$loginuser['members_id']."', to_id = '$mid', date = '".$_POST['date']."'";
        $add_member = mysql_query($insert) or die(mysql_error());       
        header('Location: index.php'); 
        exit();
    }

?>


  <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" >
<table cellspacing='0' id="booking" align="center" width="680">


<tr>
    <td>Date:</td>
    <td><input name="date" type="text"  size="10" maxlength="10"  class="form-field" /> </td>
</tr>

<tr>
    <td>&nbsp;</td>
    <td><input class="submit-button" type="submit" name="submit" value="SEND REQUEST" /></td>
</tr>
</table>
</form>
<br />

HERE IS THE TABLE STRUCTURE

CREATE TABLE IF NOT EXISTS `booking` (
  `booking_id` int(11) NOT NULL AUTO_INCREMENT,
  `from_id` int(11) NOT NULL,
  `to_id` int(11) NOT NULL,
  `date` varchar(10) NOT NULL,
  PRIMARY KEY (`booking_id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;

Try to echo the query and execute it once in the database and check what sql error you are getting. — Jenz, Nov 20 '13 at 06:07
Can you paste the results of `var_dump($_GET);` and `var_dump($_POST);` in your question? Also, can you show `print $sql;` right before you call `mysql_query`? — Chris Baker, Nov 20 '13 at 06:16
If your `$mid;` is coming from a POST form, which is what I can tell by your `if (isset($_POST['submit']))` then try using `$mid = $_POST['m'];` if you're using a GET method then use `if (isset($_GET['submit']))` — Funk Forty Niner, Nov 20 '13 at 06:16
@Chris Whatever the benefits of PDO, your flag waving above is not helping the OP get _this_ code working — , Nov 20 '13 at 06:17
if I echo the query, I get the value... I get this: insert into table SET from_id = '30', to_id = '13', date = '' If I copy & paste in mysql, It enters correctly... the problem seems to be when I submit my form, something must messup the the value... — user3011784, Nov 20 '13 at 06:18
@MikeW That's why I'm still participating in the question, bro. Your answer does not correct the issues either -- there simply is not enough information. In the mean time, my "flag waving" is addressing a problem in this code every bit as fundamental as the bug the OP is trying to solve. — Chris Baker, Nov 20 '13 at 06:18
the $mid comes form the $_GET['m']; from the url.... abc.php?m=13 — user3011784, Nov 20 '13 at 06:19
@user3011784 You're saying that if you copy and paste the query to mysql, it works fine? You're sure it is in the same database? — Chris Baker, Nov 20 '13 at 06:19
Try using `$add_member = mysql_query($insert) or die(mysql_error());` Which will check the return value of the query and output an error message that will help diagnose the problem. **Note:** You shouldn't be using `mysql_*()` for new code. Use `mysqli_*()` or PDO, and read up on SQL Inection too. — , Nov 20 '13 at 06:20
WHEN i do this: $add_member = mysql_query($insert) or die(mysql_error()); I don't any error.. the date gets inserted in the DB correctly, I just get 0 as the $mid value instead of 13 — user3011784, Nov 20 '13 at 06:23
[See my edited comment](http://stackoverflow.com/questions/20088596/variable-not-inserting-correctly-in-mysql-from-php#comment29929354_20088596) @user3011784 `to_id = '$mid',` — Funk Forty Niner, Nov 20 '13 at 06:24
@Fred-ii- adding the quotes only converts it to a string, which, if the field is indeed an int, will simply get converted back to an integer by the sql server. The quotes around an integer value just aren't needed. user3011784 -- can you confirm the column data type? It really sounds like some assumption is wrong -- this is clearly an oversight of some kind. — Chris Baker, Nov 20 '13 at 06:25
It shouldn't be so complicated. Post the complete PHP and HTML form code you are using. — Haywire, Nov 20 '13 at 06:25
It isn't about the HTML form. The issue has already been narrowed down -- there's something either wrong with the query, or wrong with the table structure. He says he can copy/paste the query to console and it works. Something doesn't add up here. Obviously, mysql server can handle putting integers into a table. — Chris Baker, Nov 20 '13 at 06:27
OP commented above: `if I echo the query, I get the value... I get this: insert into table SET from_id = '30', to_id = '13', date = '' If I copy & paste in mysql, It enters correctly... the problem seems to be when I submit my form, something must messup the the value` So the integer column is fine I guess. — Haywire, Nov 20 '13 at 06:28
But the form is fine, because when he "echos the query", that is a string being constructed out of the posted data. So the data is clearly making it to the script. That's where I start to question: do you have a second database set up? Are you sure? — Chris Baker, Nov 20 '13 at 06:30
Something just clicked here. OP is using `INSERT into table SET` notice anything wrong with this picture? ;-) — Funk Forty Niner, Nov 20 '13 at 06:30
The reserved word "table"... @user3011784, try this for giggles: "insert into `table` SET from_id = '30', to_id = '13', date = ''" -- added backticks around the table name. I don't think this would have the effect that you are describing, but Fred is right that "table" is a reserved word. — Chris Baker, Nov 20 '13 at 06:34
No, the OP was using `table` as a reference. The updated code in the question is `booking`. `SET` is for doing an `UPDATE`, I see no `VALUES` so how can the OP do an INSERT without VALUES? @Chris — Funk Forty Niner, Nov 20 '13 at 06:35
my "table" is actually "booking"... I didn't use the name table for my table.. I just wrote that to make things easier on here.. i shouldn't have done that.. but that's not what's causing the issue.. the rest of my data gets inserted properly in the DB.. — user3011784, Nov 20 '13 at 06:36
@Fred-ii- Check this: http://stackoverflow.com/questions/861722/mysql-insert-into-table-values-vs-insert-into-table-set — Haywire, Nov 20 '13 at 06:37
Oh, Ok... I stand corrected then. I didn't know that till now, thanks. @MikeW and Chris. — Funk Forty Niner, Nov 20 '13 at 06:37
@user3011784 Yeah, it was worth checking up on anyway :P Back to the 1,000 questions: are you **sure** there aren't multiple copies or that your queries are going to the database you expect? The thing is... there isn't a reason for the problem you describe with the details you've given. This is pretty standard stuff here. Obviously, there is some factor that isn't being taken into account. Can you post your table structure too? — Chris Baker, Nov 20 '13 at 06:38
I'm thinking now, since OP is using `method="post"` in the form, and there's a GET request, wouldn't it be better (or work) to use `$mid = $_REQUEST['m'];` ? I'm baffled as to why none of our suggestions have helped to solve this one. — Funk Forty Niner, Nov 20 '13 at 06:39
@user3011784 Can u try `'INSERT INTO booking (from_id, to_id, date) VALUES ('.$loginuser['members_id'].' , '.$mid.', '.$POST['date'].');'` ? — Haywire, Nov 20 '13 at 06:40
It boils down to the database, because he showed, by echoing the query, that all the data is making it to the script just fine. His query is fine. He copy/pastes the query, and it works fine. So that leaves only one possibility I can see -- there is more than one database, or there is something going wonky with the column types. If it were an issue with the form, or $_REQUEST vs $_GET vs $_POST, his query would reflect that because it would be missing data. — Chris Baker, Nov 20 '13 at 06:41
The only other thing I can see is that `$mid = $_GET['m'];` is outside of `if (isset($_POST['submit']))` and is not part of `$insert = "insert into booking...` — Funk Forty Niner, Nov 20 '13 at 06:45
Table is good (except you should use some datetime datatype for storing dates, some other day ;-) ). Now try the alternate query I posted above. — Haywire, Nov 20 '13 at 06:45
There's also the whole storing a password in cookies thing, but... another day. I am looking at this and I just don't see it. I haven't set up a full reproduction yet, but pretty much, and it works on my server using all the details we've discussed here. My suggestion is to create a minimal script with only a database connection and try running the query there, see how it goes. We're missing some detail from the picture, I think. I gotta go to bed -- good luck :D — Chris Baker, Nov 20 '13 at 06:48
Check table structure first that you are inserting in right format. — Ali, Nov 20 '13 at 06:53
@user3011784 -- try this code, out of curiosity: https://eval.in/70280 -- you will of course have to supply the database credentials. For one, that's PDO, so you can see how easy it is to use, but two I am curious if you see the same problem. — Chris Baker, Nov 20 '13 at 06:57
@haywire I'm trying your new insert code like this $insert = "INSERT INTO booking (from_member_id, to_member_id, date) VALUES ('".$loginuser['members_id']."' , '".$mid."', '".$POST['date']."');" $add_member = mysql_query($insert) ; But i keep getting Parse error: syntax error, unexpected T_VARIABLE — user3011784, Nov 20 '13 at 07:05
@user3011784: An `_` is missing in POST variable. It should be `$_POST['date']` instead of `$POST..` and see you name the columns correctly. The query is `"INSERT INTO booking (from_id, to_id, date) VALUES ('".$loginuser['members_id']."' , '".$mid."', '".$_POST['date']."');"` — Haywire, Nov 20 '13 at 07:19
@haywire OK, did that with the ( ) VALUES ( ) ... same exact problem as before... — user3011784, Nov 20 '13 at 07:28
@user3011784 Now `var_dump($mid)` before and after $insert statements and echo the insert statement and post the output on pastebin and give us a link. — Haywire, Nov 20 '13 at 07:32
if I put var_dump($mid) before and after the insert, my php page won't load. it says Parse error: syntax error, unexpected T_VARIABLE on line 57 (line 57 is the $insert line). If I take out both var_dump, it goes back to normal — user3011784, Nov 20 '13 at 07:39

praveen · Answer 1 · 2013-11-20T06:23:00.333

-4

Try in this way

if (isset($_POST['submit'])) 
{ 
$mid = $_GET['m'];
$insert = "insert into table SET from_id = '".$loginuser['members_id']."', to_id = '". $mid ."', date = '".$_POST['date']."' ";
    $add_member = mysql_query($insert);     
}

edited Nov 20 '13 at 06:23

answered Nov 20 '13 at 06:10

praveen

286
1
8

2

This is not an answer – Sterling Archer Nov 20 '13 at 06:10
Post questions asking for clarification as a comment. – Chris Baker Nov 20 '13 at 06:12
This code is no different than his. You moved the variable assignment within the if statement. – Chris Baker Nov 20 '13 at 06:28

variable not inserting correctly in mysql from PHP

1 Answers1