...

View Full Version : MYQSL UPDATE not working



Atrhick
11-29-2011, 06:22 PM
Can you guys please tell me why this is not updating the database?



<?php
if(isset($_POST['agent'])){

$buy_sales_agent = $_POST['buyers'];
$seller_sales_agent = $_POST['sellers'];
$sales_agent = $_POST['sales_agent'];

$query12 = " UPDATE lead_partners_pages "
. " SET buy_sales_agent = $sales_agent, seller_sales_agent = $sales_agent "
. " WHERE partner_id_buyer = $buy_sales_agent, afid_seller_name = $seller_sales_agent ";


mysql_query($query12);


}else
{
echo "Something went wrong!! you broke the script!<br/>";
}
?>

djm0219
11-29-2011, 07:01 PM
You aren't checking for a failure of the MYSql query so you don't know what's going wrong with it. Your WHERE statement is also not correct. If you want both of those values to be tested to be equal you need to use AND between them not a ,


WHERE partner_id_buyer = $buy_sales_agent AND afid_seller_name = $seller_sales_agent

Try changing your call to mysql_query to


mysql_query($query12) or die(mysql_error());

Atrhick
11-29-2011, 08:22 PM
still not working here is the complete code this is stumping me


<div id="sign_up">
<div id="hidden_agent" style="font-family:Tahoma, Geneva, sans-serif; font-size:12px;">
<h1 style="font-size:15px"><strong>Assign Sales Agent to Company</strong></h1>
<p><strong style="color:#990000;">NOTICE:</strong> you can only assiagn the agent to one buyer or seller company at a time!</p>
<?php
if(isset($_POST['agent'])){

$buy_sales_agent = $_POST['buyers'];
$seller_sales_agent = $_POST['sellers'];
$sales_agent = $_POST['sales_agent'];

$query121 = " UPDATE lead_partners_pages "
. " SET buy_sales_agent = '$sales_agent', seller_sales_agent = '$sales_agent' "
. " WHERE partner_id_buyer = '$buy_sales_agent' AND afid_seller_name = '$seller_sales_agent' ";


$addagent = mysql_query($query121) or die(mysql_error());

if($addagent)
{
echo"<span style=\"color:#11d728;\">It worked!</span<p></p>";
}

else
{
echo "<span style=\"color:#11d728;\">Something went wrong!! you broke the script!</span><p></p>";
}

}else
{
echo "<span style=\"color:#11d728;\">Please fill out the info and hit submit.</span><p></p>";
}
?>
<form method="post" action="">
<select name="buyers">
<option selected="selected">Select Buying Company</option>
<option>CIQFY</option>
<?php foreach($PartnerClients as $Partnerclientname): ?>
<?php { ?>
<?php echo "<option>" . $Partnerclientname['partner_id_buyer'] . "</option>";?>
<?php } ?>
<?php endforeach; ?>
</select>

<select name="sellers">
<option selected="selected"> Select Selling Company </option>
<option>CIQFY</option>
<?php foreach($afid_sellerClients as $afid_sellerclientname): ?>
<?php { ?>
<?php echo "<option>" . $afid_sellerclientname['afid_seller_name'] . "</option>"; ?>
<?php } ?>
<?php endforeach; ?>
</select>
<hr/>
<label>Sales Agent Name:</label><br/>
<input type="text" name="sales_agent"/><br/>
<input style="width: 93px; height: 31px; font-size:14px; margin-top:7px" type="submit" name="agent" value="Submit"/>
</form>
</div>
</div>

Old Pedant
11-29-2011, 08:23 PM
But the *FIRST* thing to do is DEBUG DEBUG DEBUG.

Why does it seem to me that PHP programmers don't do this automatically??



$query12 = " UPDATE lead_partners_pages "
. " SET buy_sales_agent = $sales_agent, seller_sales_agent = $sales_agent "
. " WHERE partner_id_buyer = $buy_sales_agent, afid_seller_name = $seller_sales_agent ";

echo "<hr/>DEBUG SQL: " . $query12 . "<hr/>\n";

...

Aside from the missing AND that DJM noted, I would bet that there are also at least some, if not many, missing apostrophes. I would bet that if a field is named afid_seller_name then indeed you are storing a name there. And that would mean you need apostrophes around $seller_sales_agent.

If you would put in the DEBUG code and show us the results of that, we could tell you for sure. (You also have zero protection against SQL Injection Attacks there, but that's another issue.)

Old Pedant
11-29-2011, 08:24 PM
Talk about posting at nearly the same time! LOL!

Okay, so *did* the query die with an error message?

If not, then show the output of the debug I asked for.

Atrhick
11-29-2011, 08:35 PM
it did not die it exacted this like of the code



{
echo"<span style=\"color:#11d728;\">It worked!</span<p></p>";
}


but once i check the database i don't see anything updated

Old Pedant
11-29-2011, 08:43 PM
Once again, ADD IN THE DEBUG CODE and show us what it shows you.

Atrhick
11-29-2011, 08:50 PM
Here is the report I dont see any problems with it



<hr/>DEBUG SQL: UPDATE lead_partners_pages SET buy_sales_agent = 'Atrhick', seller_sales_agent = 'Atrhick' WHERE partner_id_buyer = 'CompareInsuranceQ' AND afid_seller_name = 'DataTree' <hr/>

Atrhick
11-29-2011, 09:22 PM
I got it to work. I have to make another query and run it in tandem



<?php
if(isset($_POST['agent'])){

$buy_sales_agent = $_POST['buyers'];
$seller_sales_agent = $_POST['sellers'];
$sales_agent = $_POST['sales_agent'];

$query121 = " UPDATE lead_partners_pages "
. " SET buy_sales_agent = '$sales_agent' "
. " WHERE partner_id_buyer = '$buy_sales_agent' ";

$query122 = " UPDATE lead_partners_pages "
. " SET seller_sales_agent = '$sales_agent' "
. " WHERE afid_seller_name = '$seller_sales_agent' ";


$addagent1 = mysql_query($query121) or die(mysql_error());
$addagent2 = mysql_query($query122) or die(mysql_error());

if($addagent1 && $addagent2)
{
echo "<span style=\"color:#11d728;\">The recoed has been updated!</span><p></p>";
}

else
{
echo "<span style=\"color:#11d728;\">Something went wrong!! you broke the script!</span><p></p>";
}

}else
{
echo "<span style=\"color:#11d728;\">Please fill out the info and hit submit.</span><p></p>";
}
?>

Old Pedant
11-29-2011, 09:42 PM
AHHH! Yes, UPDATE affects *ALL* records in the same way.

So if you didn't have any *one* record that met both of the ANDed conditions, then of course no records would be affected.

Atrhick
11-30-2011, 03:20 PM
I guess I am getting better with MYSQL, in August when i joined here i was not good at all with MySQL now I am getting it :)

I just have to get my head around relational databases now.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum