Insert And Then Update

TKB

I have the following code but it doesn't work. Does anybody know?

Thank you in advance for your effort.

<?php
$query = "INSERT INTO transportationcharges (TransportationUnit)
VALUES ('$TransportationUnit')";
$updateSQL = $conn->GetUpdateSQL($query, $TransportationUnit);
$conn->Execute($updateSQL);

stolzyboy

this line is wrong

$query = "INSERT INTO transportationcharges (TransportationUnit)
VALUES ('$TransportationUnit')";

change it to

$query = "INSERT INTO transportationcharges (TransportationUnit)
VALUES ('" . $TransportationUnit . "')";

TKB

Dear stolzyboy,

I have corrected the insert as per your quote but what I get is "Fatal error: Call to a member function GetUpdateSQL() on a non-object in C:\wamp\www\Asbestos\PlaceOrder.php on line 181".

How can I update the db?

Awaiting yours with many thanks.

stolzyboy

i wouldn't know as getupdatesql() is obv a user-created function...

typically, if you don't use a function you'd do this, obviously you need to connect to the mysql server and then the db, so i'll leave that out

$query = "INSERT INTO transportationcharges (TransportationUnit) VALUES ('" . $TransportationUnit . "')";
$result = mysql_query($query)  or die("Error is: <b>" . mysql_error() . "</b><br><br>");

echo "Items inserted: <b>" . mysql_affected_rows() . "</b>";

eriksmoen

I have no idea what class you are using, but I can't see how $TransportationUnit in the GetUpdateSQL function is relevant, since it is already being called in $query. Also, why Execute() is called to run the function instead of running it from GetUpdateSQL()...

would it be possible to post the GetUpdateSQL() and Execute class functions in here so we can examine it? Thanks.

Otherwise, stolzyboy's solution should work just fine, depending on what other functions you have accessing that class file...

TKB

Stolzyboy/Eriksmoen,

Thank you for your reply, however my problem isn't resolved. When this code is inserted in php page and html page (has a $_POST) the query is executed. When I write this code in my php page with no html page involved the insert query isn't executed. I don't know if I made myself clear but as a beginner I can be excused.

Anyway I won't bother you anymore.

Thanks for your effort.

liquorvicar

stolzyboy wrote:

this line is wrong

$query = "INSERT INTO transportationcharges (TransportationUnit)
VALUES ('$TransportationUnit')";

change it to

$query = "INSERT INTO transportationcharges (TransportationUnit)
VALUES ('" . $TransportationUnit . "')";

Actually, no, it's not wrong. PHP will parse any strings in double-quotes and recognise any variables. The first example will work equally well as the second.

TKB - Are you creating your $conn object anywhere?

TKB

Liquorvicar,

I have tested both codes and know that they work but let's take things from the beginning.

I have the following pages:

PlaceOrder.html which has the following:

<form name="PlaceOrder" id="form2" method="post" Action="PlaceOrderFinal.php">
<table width="589" border="1" cellpadding="5" cellspacing="15" bgcolor="#DDDDDD">
<tr>
<td>Category Name : </td>
<td><input name="CategoryName" type="text" id="CategoryName" /></td>
</tr>
<tr>
<td>Insucance:</td>

PlaceOrder.php which has the following in order for my client to make the insertions:

<?php
include 'connectdb.php;

include 'View_my_Order.php';

<?php
mysql_query ("INSERT INTO transportationcharges (TransportationUnit)
VALUES
('$_POST[TransportationUnit]')");
mysql_query($query) or die(mysql_error());
mysql_close();
?>

etc.

and the View_my_Order.php which has the following in order for my client to see his/her order:

Which has select statements retrieved from db, date and the insertions the client placed with the place order.php

Unfortunately the insertions are not placed in the db. How can I do that?

Thank you in advance for your effort.

stolzyboy

liquorvicar wrote:
Actually, no, it's not wrong. PHP will parse any strings in double-quotes and recognise any variables. The first example will work equally well as the second.

TKB - Are you creating your $conn object anywhere?

yeah, i know it'll parse anything in double quotes, however for sanity's sake, sooner or later, he's gonna use $_POST['variable'] and it's not going to like that, so why push him down the wrong road in the beginning and why make the interpreter do more work than it has too??

stolzyboy

so, you are saying NOTHING is going into the DB but there are NO mysql errors??

have you simply echo'd out the query to make sure you are getting the variables to come out right?

liquorvicar

stolzyboy wrote:
yeah, i know it'll parse anything in double quotes, however for sanity's sake, sooner or later, he's gonna use $_POST['variable'] and it's not going to like that, so why push him down the wrong road in the beginning and why make the interpreter do more work than it has too??

Yes but you shouldn't be using $POST['variable'] straight into queries like that so if it doesn't like that it may be a good thing - although you can always just enclose it in curly braces e.g.

 $query = "INSERT INTO transportationcharges (TransportationUnit)
VALUES ('{$_POST['TransportationUnit']}')";

.
It's not a massive strain on the interpreter. If you're using double-quoted strings it's parsing them anyway.

TKB

Most probably the reason why the insertions are not effected is because I'm doing in the same page multiple insertions. Am I correct?

Because when I used abovementioned codes it worked but only for insertions to 1 table.

Do you happen to know what's the code to make insertions to multiple tables?

Thanks a lot for your help and effort.

stolzyboy

liquorvicar wrote:
Yes but you shouldn't be using $POST['variable'] straight into queries like that so if it doesn't like that it may be a good thing - although you can always just enclose it in curly braces e.g.
 $query = "INSERT INTO transportationcharges (TransportationUnit)
VALUES ('{$_POST['TransportationUnit']}')"; 
.
It's not a massive strain on the interpreter. If you're using double-quoted strings it's parsing them anyway.

there is nothing saying you can't do

$POST['whatever'] = trim($POST['whatever']);

or whatever "cleaning" methods you have...

and no, if you aren't hopping out of the string, it's not trying to decide if it's a variable or a regular string...

but whatever, this isn't solving his problems 🙂

as far as your question about multiple table insertions... do you have more than one query running when the page runs??

just for future reference, it's hard to decipher sometimes when we only get snippets of code, obviously we don't want the whole thing, but if you are explaining you are doing something somewhere, it'd be nice to see the code to make it easier for us to help you

thanks,
stolzyboy

TKB

Yes I do have more than 1 query since I can't find the way to put the insertions into 1.

I have 5 tables and want to make the relative insertions. Do you happen to know the sql syntax?

Thanks again,
TKB

liquorvicar

TKB - are you getting any SQL errors at all?
If your data is not being inserted into the DB there's a number of things that could be wrong.
Your logic could be flawed so the section of the script where the second insert is happening is not being run, try echo-ing out each query instead of executing it and see what's happening.
Or, you could have SQL errors. Try adding

$sql = "some SQL string" or die(mysql_error());

to every query to see what if any error is being thrown.

ps remember to remove this in production code!

There is no actual SQL to do relative inserts across multiple tables (I'm assuming you mean where the primary key of the first insert is used as a foreign key across the other inserts etc). Try something like this:

- Insert first query,
- Get ID from last insert,
- Insert next query with ID from last query, etc

there is nothing saying you can't do

$POST['whatever'] = trim($POST['whatever']);

Yes, there is. Good coding practice tells you to stay well clear if code like this.

stolzyboy

good coding practice??

i typically like to KNOW where my variables are coming from... setting a post variable to different values and sanitizing them is perfectly fine, just because you REUSE the $_POST does NOT make it a security risk whatsoever

you can ruin a regular variable just as easy if you don't sanitize, simply doing

$variable = $_POST['variable'];

does nothing more than making a little less typing, as long as you are sanitizing, it makes no difference WHAT variable you use

stolzyboy

oh, and TKB, there is nothing wrong with running 5+ queries... if that's what you need to do, vBulletin probably runs 30-40 queries on any given page request, 5 is nothing

if these queries, like liquorvicar said are in different if/else type blocks, maybe you aren't even getting to them, echo them out and make sure you are even getting into the if/else blocks

stolzyboy

liquorvicar

But if you have a script several hundred lines long (or any script more than a page in length), how is someone (whether it's actually someone else or you in 6 months time) to know that $_POST['variable'] is sanitised down on line 78 without going back over the whole script? If you want to know where it's coming from (which I agree is a very good habit to be in), why not call you cleaned version $cleanedPost['variable'] or some such?
As you say, this isn't helping the original poster much and has drifted slightly off-topic :rolleyes: But it's a healthy debate to have.

stolzyboy

yes, i use $cleanedPOST['variable'] methods as well, which brings us still back to the ['variable'] quotes issue, i guess i didn't figure it mattered if i used $POST, $WHATEVER for our discussion 😉

yeah, it's probably a good thing for him to read over, but hopefully we can get his issues solved

stolzyboy

TKB

It's unbelievable, I've now got an error "Query was empty"????

What's this?

My code is fairly simple only for each of the 5 tables and I can't seem to figure it out why insertions aren't working.

<?php

mysql_query ("INSERT INTO categories (CategoryName)
VALUES
('$_POST[CategoryName]')");
mysql_query($query) or die(mysql_error());
echo ($query_row[CategoryName]);
mysql_close();