INSERT QUERY

[datoshway]

I need to insert a query but with the ID # from another table.  Here is the query I am using to select which works fine.

 

// get reviews
		$strQuery			= sprintf("SELECT * FROM messages WHERE intItemID = %d", 
										intval($intProductID));
		$queryGetReviews	= db_query($strQuery);

 

However my insert doesn't seem to be picking up the %d. 

//query to insert data into table
$sql = "
	INSERT INTO messages
	SET
	firstname = '$name',
	message = '$message' WHERE intItemID=%d"
	;
$result = mysql_query($sql);
if(!$result)
{
	echo "Failed to insert record";
}
else
{
	echo "Record inserted successfully";
}
}

 

Please help!

 

[GizSho]

I'm not very good at PHP but I am pretty sure the error is that you are not setting a variable for %d

 

$sql = sprintf("
	INSERT INTO messages
	SET
	firstname = '$name',
	message = '$message' WHERE intItemID=%d", $record)
	;

 

Should be:

 

$sql = sprintf("
	INSERT INTO messages
	SET
	firstname = '$name',
	message = '$message' WHERE intItemID=%d", $record);

[Muddy_Funster]

1. Do you have a REALLY good reason for using SELECT *?

2. What is %d ? it's not a number, so should be wraped in quotes regardless.  It also includes a wildcard for use with the LIKE statement which, obviously, is different from [=]. Could you tell us what %d is reffering to exactly.

[datoshway]

Tried that, that didn't work.

 

[BlueSkyIS]

1. Do you have a REALLY good reason for using SELECT *?

2. What is %d ? it's not a number, so should be wraped in quotes regardless.  It also includes a wildcard for use with the LIKE statement which, obviously, is different from [=]. Could you tell us what %d is reffering to exactly.

[datoshway]

The select is not the issue, my select statement is fine and working, my insert is just not inserting the ID number.  THE %d is the ID number for the product page we are on, that is what i'm looking to store, this ID number is stored in a separate database. For some reason I can't insert that number into table messages, even though it queried it for the select.

 

 

[PFMaBiSmAd]

INSERT queries don't have WHERE clauses, what exactly are you trying to do?

[datoshway]

My database looks like this.

 

id | message | firstname | intItemID

 

I'm displaying the messages that intItemID = %d which is the ID of a item number from a items table.  So it's displaying properly,  however there is a form to insert while on this page which is working fine but not inserting the ID number (intItemID) for the product page we are on. 

 

[datoshway]

Here is everything i'm dealing with .

if(!empty($_POST) && isset($_POST))
{
//make variables safe to insert
  $name = mysql_real_escape_string($_POST['name']);
  $intItemID = mysql_real_escape_string($_POST['intItemID']);
  $message = mysql_real_escape_string($_POST['message']);

//query to insert data into table
$sql = "
	INSERT INTO messages
	SET
	firstname = '$name',
	intItemID = '%d',
	message = '$message'"
	;
$result = mysql_query($sql);
if(!$result)
{
	echo "Failed to insert record";
}
else
{
	echo "Record inserted successfully";
}
}
?>

<?php
if (db_num_rows($queryGetReviews) > 0) {
while ($objRow= db_fetch_object($queryGetReviews)) { //open while loop
                        $message = strip_tags(stripslashes($objRow->message));
                        $firstname = stripslashes($objRow->firstname);
   ?>
   <br />
  <strong style="font-size:18px; color:#42919a;"><?php  echo $firstname;?><br /></strong>
<strong style="font-size:16px; color:#ccc;"><?php  echo $message;?><br /></strong><br />
            <img src="../../line_horz2.png" width="766" height="2" /><br />
<?php 
} //close while loop

} else { //close IF and add else statement
//throw error message
echo "error has occured or no rows returned<br/>".mysql_error();
}?>

 

And my controller

<?php

session_start();

db_connect();
db_select();
processLogin();

$intProductID		= intval(getVariable("id"));

if (is_numeric($intProductID)) {
	$strQuery				= sprintf(	"SELECT strName, txtDescription, txtComments, dblPrice, dblVolume, intMOQ, intManufacturerID, intTimesPurchased, boolCustom, strThumbFilename, txtKeywords, boolActive, dtEntered FROM tblItems WHERE intID = %d", 
										intval($intProductID));
	$queryGetProduct		= db_query($strQuery);

	if (db_num_rows($queryGetProduct) > 0) {
		$arrParameters	= array();
		$arrRow			= db_fetch_assoc($queryGetProduct);
		$arrRow			= importVariables($arrRow);

		extract($arrRow);

		db_free_result($queryGetProduct);




		// get reviews
		$strQuery			= sprintf("SELECT * FROM messages WHERE intItemID = %d", 
										intval($intProductID));
		$queryGetReviews	= db_query($strQuery);


	} else {
		header("Location: " . STR_RELATIVE_PATH);
	}
}

?>

 

 

 

[PFMaBiSmAd]

The %d syntax is a sprintf() format specifier. It gets replaced with the correct parameter from the sprintf() function call, which you are not using.

 

Since you aren't using sprintf() to build your query string, why not just put the $intItemID variable into the $sql = ""; statement, the same as you are doing with the $name and $message variables?

 

 

[datoshway]

I tried that but it still records it as NULL.

 

//make variables safe to insert
  $name = mysql_real_escape_string($_POST['name']);
  $intItemID = mysql_real_escape_string($_POST['intItemID']);
  $message = mysql_real_escape_string($_POST['message']);

//query to insert data into table
$sql = "
	INSERT INTO messages
	SET
	firstname = '$name',
	intItemID = '$intItemID',
	message = '$message'"
	;
$result = mysql_query($sql);
if(!$result)
{
	echo "Failed to insert record";
}
else
{
	echo "Record inserted successfully";
}
}
?>

 

Anyone want to make some quick money?  PM me.

[Muddy_Funster]

still not getting this %d thing at all, and we obviously have different deffinitions of "working fine".  However, give this code a try if you havn't already.

INSERT INTO messages (firstname, message, intItemID) VALUES ('$name', '$message', $intItemID)"

[datoshway]

That didn't work.  %d is simply what i'm using to define the ID# for the product page we are viewing, this number is stored in a table called tblItems

 

[PFMaBiSmAd]

Nothing you have posted shows the form that is submitting the data, are you sure $_POST['intItemID'] contains what you expect?

 

Your current code would be inserting a value if there is one. From my signature -

Debugging step #1: To get past the garbage-out equals garbage-in stage in your code, you must check that the inputs to your code are what you expect.

[leitning_strike]

You're using UPDATE syntax for an INSERT query

 

$sql = "
	INSERT INTO messages
	SET
	firstname = '$name',
	intItemID = '%d',
	message = '$message'"

 

should be

 

$sql = "INSERT INTO 
messages (firstname, intItemID, message) 
VALUES ('$name', '%d', '$message')";

[Pikachu2000]

Either syntax is valid for a single-record insert.

[datoshway]

Still not recording intItemID... This is now what we have..

if(!empty($_POST) && isset($_POST))
{
//make variables safe to insert
  $name = mysql_real_escape_string($_POST['name']);
  $intItemID = mysql_real_escape_string($_POST['intItemID']);
  $message = mysql_real_escape_string($_POST['message']);

//query to insert data into table
$sql = "INSERT INTO 
messages (firstname, intItemID, message) 
VALUES ('$name', '%d', '$message')";
$result = mysql_query($sql);
if(!$result)
{
	echo "Failed to insert record";
}
else
{
	echo "Record inserted successfully";
}
}

[Pikachu2000]

Let's see exactly what the variable holds before going too much further. Add the indicated line and see what it returns.

if(!empty($_POST) && isset($_POST))
{
//make variables safe to insert
  $name = mysql_real_escape_string($_POST['name']);
  $intItemID = mysql_real_escape_string($_POST['intItemID']);
  $message = mysql_real_escape_string($_POST['message']);

echo "<br>POST value: {$_POST['intItemID']}<br>\$intItemID value: $intItemID<br>";  // <---- Add this line

//query to insert data into table
$sql = "INSERT INTO. . .

 

EDIT: closed BBCode tag properly . . .

[datoshway]

It came back with this:

 

POST value:

$intItemID value:

Record inserted successfully

[Pikachu2000]

Then that's the problem. The value isn't being sent from the previous form. Post the code for that form, and how the values are populated.

[Muddy_Funster]

Just had a thought, if the $intItemID is taken from the "current page" are you trying to use $_POST to get a value from the page header?

[datoshway]

Here is the form

  <form id="myForm" action="data.php" method="post">
    <fieldset>
      <div>
        <label>Name*</label>
        <input type="input" name="name" class="reviews"/>
      </div>
      <div>
        <label>Review*</label>
        <textarea rows="15" cols="15" name="message" class="reviews"></textarea>
      </div>
      <div>
        <input type="submit" value="Submit" />
      </div>
    </fieldset>
  </form>

[Pikachu2000]

There's no form field named 'intItemID'. Where is that value supposed to come from?

[datoshway]

It's not supposed to be displayed, it's coming from another table. Even if I add a field for intItemID, nothing happens.

 

[leitning_strike]

Still don't get where you're getting %d from? Don't you need to do:

 

$sql = sprintf("INSERT INTO messages (firstname, intItemID, message) 
VALUES ('$name', '%d', '$message')", $intItemID);