MYQSL UPDATE not working

[Atrhick]

Can you guys please tell me why this is not updating the database?

PHP Code:

<?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]

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

PHP Code:

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


[Atrhick]

still not working here is the complete code this is stumping me

PHP Code:

<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]

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]

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]

it did not die it exacted this like of the code

PHP 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]

Once again, ADD IN THE DEBUG CODE and show us what it shows you.

[Atrhick]

Here is the report I dont see any problems with it

PHP Code:

<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]

I got it to work. I have to make another query and run it in tandem

PHP Code:

<?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]

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]

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.