Jump to content


Photo

variables and MySQL INSERT


  • Please log in to reply
5 replies to this topic

#1 klainis

klainis
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 08 June 2006 - 07:57 PM

I can't seem to get variables to work with my query statement. Everything works fine if I manually input what I want in the PHP code, but not from the variables. I have added various echo statements just to make sure that all the variables actually hold something, and they do. I have also run some select statements to make sure that the database is actually connected and it is. I get no error message from this, it just leaves a blank screen. Here is the code.

<html>
<head>
    <title>Book Entry Results</title>
</head>
<body>
<h1>Book Entry Results</h1>
<?php
    //create short variable names
    $isbn=$_POST['isbn'];
    $author=$_POST['author'];
    $title=$_POST['title'];
    $price=$_POST['price'];
    
    if(!$isbn || !$author || !$title || !$price)
    {
        echo "You have not entered all the required details.<br />"
            ."Please go back and try again.";
        exit();
    }
    
    if(!get_magic_quotes_gpc())
    {
        $isbn=addslashes($isbn);
        $author=addslashes($author);
        $title=addslashes($title);
        $price=doubleval($price);
    }
    
    $db = new mysqli('localhost', 'username', 'password', 'books');
    
    if (mysqli_connect_errno())
    {
      echo "Error: Could not connect to database.  Please try again later.";
      exit();
    }
    $query = "INSERT INTO books VALUES
            ('$isbn', '$author', '$title', '$price')";
    
    //echo $query;
    
    //$result = $db->query($query);
    $result = mysqli_query($db, $query);
    
    //echo $result;
    
    if($result)
        echo $db->affected_rows.' book inserted into database.';
    else
        echo "This sucks";
        
    $db->close();
    
?>
</body>
</html>

Any help would be greatly appreciated.

#2 Buyocat

Buyocat
  • Members
  • PipPipPip
  • Advanced Member
  • 267 posts

Posted 08 June 2006 - 08:05 PM

The problem I see with your insert statement is the following, it specifies the values to be inserted but not the columns to insert the values to. You need to construct the statement like this:

INSERT INTO tablename (col1, col2, col3) VALUES ('val1', 'val2', 'val3')

hope that helps
Looking for some easy-to-use tools?  Try these, https://sourceforge....jects/utils-php -- I made them myself.  They're distinct tools which are easy to understand and use.  See some examples uses at http://www.anotherearlymorning.com

#3 klainis

klainis
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 08 June 2006 - 08:39 PM

[!--quoteo(post=381570:date=Jun 8 2006, 01:05 PM:name=Buyocat)--][div class=\'quotetop\']QUOTE(Buyocat @ Jun 8 2006, 01:05 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
The problem I see with your insert statement is the following, it specifies the values to be inserted but not the columns to insert the values to. You need to construct the statement like this:

INSERT INTO tablename (col1, col2, col3) VALUES ('val1', 'val2', 'val3')

hope that helps
[/quote]


I have actually tried it that way. I have also tried

$query = "INSERT INTO books 
                set isbn = '$isbn',
                author = '$author',
                title = '$title',
                price = '$price'";

I have even tried not bothering with the query variable and just plugging the full query into

$result = mysqli_query($db, "");

And no matter how I form it, I still get the blank page, but if I replace the variables with words/numbers the database is updated and the results are shown.


Thanks,
Robert

#4 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 08 June 2006 - 08:43 PM

so you are telling us that
$query = "INSERT INTO books (isbn,author,title,price) VALUES ('$isbn','$author','$title','$price')";
$result = mysql_query($query); 
does not work?
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#5 nogray

nogray
  • Members
  • PipPipPip
  • Advanced Member
  • 930 posts
  • LocationSan Francisco CA

Posted 08 June 2006 - 08:52 PM

use the code Crayon Violent suggested and print out any errors, like this

$query = "INSERT INTO books (isbn,author,title,price) VALUES ('$isbn','$author','$title','$price')";
$result = mysql_query($query) or die(mysql_error()); 

The error message should tell you what's wrong, if you need more help, you can post it here.

NoGray.com


#6 klainis

klainis
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 08 June 2006 - 09:07 PM

It was the mysqli_error($db) function that made me realize I was sending an invalid character. I feel a bit dumb since it was such a small mistake, but I guess it's useful for learning. Thank you all for the help, I spent 3 hours trying to find the answer to this question with Google searches, and all I really had to do was post here.



Thanks,
Robert




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users