Error in my select and SET query - Help please

[bambinou1980]

Hello,

 

I am trying to SET a value based on my SELECT but the output error is:

Database query failed. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' SET has_reseller_been_paid = 'PAID'' at line 13

 

 

  $query = "SELECT   r.resellers_id,    o.orders_id,    o.order_status,   o.has_reseller_been_paid,               SUM(o.reseller_earnings) as total  FROM orders AS o  LEFT JOIN resellers as r  ON r.resellers_id = o.orders_resellers_id  WHERE o.due_date BETWEEN '2010-01-01' AND NOW()  AND o.order_status RLIKE '[[:<:]]Order Completed[[:>:]]'  AND o.has_reseller_been_paid RLIKE '[[:<:]]UNPAID[[:>:]]'  AND resellers_id = $reseller_id  SET               has_reseller_been_paid = 'PAID'";                 $result = mysqli_query($connection, $query);

 

 

 

 

 

I have checked my database and the columns are there.

 

I think it is my synthax, any idea where I went wrong please?

 

 

 

Also a quick question, if you have 3 queries on one page, it is ok to use the same  $result variable each time or will it causes problem and instead I shoulod rename it to $query1, $query2,$query3 and

 

$result1 = mysqli_query($connection, $query1

$result2 = mysqli_query($connection, $query2

$result3 = mysqli_query($connection, $query3

 

 

Thank you,

[Ch0cu3r]

Why are you using a SET clause in a SELECT query?  SET is used for setting values in UPDATE and INSERT queries, not SELECT queries

 

What are you storing in the has_reseller_been_paid column?

[bambinou1980]

Thank you so much! I did not even realised I used a select instead of update....:-((((

 

About the has_reseller_been_paid, it is either set to "PAID" OR "UNPAID".

 

Let me try and see if it works by changing it to update...:-)

[bambinou1980]

Unfortunately, I have not managed to get it work , I changed slightly my query but still get an error:

  $query2  = "UPDATE orders SET 
   has_reseller_been_paid = '%PAID%', 
  WHERE due_date BETWEEN '2010-01-01' AND NOW()
  AND order_status = '%Order Completed%'
  AND has_reseller_been_paid = '%UNPAID%'
  AND orders_resellers_id = $reseller_id";
  
  $result2 = mysqli_query($connection, $query2);

 

The error I am getting is:

Database query failed. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE due_date BETWEEN '2010-01-01' AND NOW() AND order_status = '%Order ' at line 3

[mac_gyver]

your SELECT query, with the various conditions in the WHERE clause is for finding records that match those conditions, for display purposes.

 

unless your intent is to update all the matching records from UNPAID to PAID, which in real life usually doesn't happen all at once, you wouldn't do an UPDATE query this way. after you display the UNPAID recored(s) for the selected reseller, you/someone would pick (using a check-box) the specific record(s) you want to update from UNPAID to PAID. your form would submit the id's of the records that were checked and you would use the ids in the WHERE clause to update just those records. the only thing in the WHERE clause would be the id comparison. "UPDATE orders SET has_reseller_been_paid = 'PAID' WHERE id IN(...)" the ... would be a comma separated list of id(s) (one or more) that were submitted from the form.

 

 

you do understand that the % in these values - '%PAID%', '%Order Completed%', and '%UNPAID%' are wildcard characters that are only used in the LIKE syntax? if these strings are the exact values stored in the database, you would not use any wildcard. what you have in the query above would require that the % characters be stored in with your data for a match to occur, which they are not.

 

btw - the sql syntax error you are getting is because you have an extra comma something at the end of - has_reseller_been_paid = '%PAID%', <--- not sure if that is a comma or a period, which i'm pretty sure you had a problem with in a previous thread. are you looking at the errors and at the place in the query near to where the error is pointing out a problem?

Edited August 27, 2015 by mac_gyver

[bambinou1980]

Thank you so much about the comma....yes it worked without it. Do you know a good tool I could use that helps building queries please so I do not have to look at all those tiny details?

Regarding setting multiple ids from PAID to UNPAID, yes this is exactly what I wanted to do, update all the payments from UNPAID to PAID. The reason being, in that table you have all the monthly earnings of each resellers added as separated total(grouped by reseller ids). 1 time per month, the site owner will pay each reseller and upon clicking on the button PAID, all the earnings that are calculated on different earning sheets will all change from UNPAID to PAID.

 

Thanks again!

 

Ben

[mac_gyver]

Do you know a good tool I could use that helps building queries ...

 

 

that tool would be your brain and the documentation for what you are doing. you have to learn the basic syntax for php or (my)sql before you can use that syntax to write your code and your sql queries. knowing that commas go between lists of same meaning things (columnA = 'valueA', columnB = 'valueB') and aren't used on the end of that list, takes learning the meaning of what you are doing and of thinking about the context when you are doing it.

 

you also have to look at your code and sql queries when you get an error in them and try to find and fix what's wrong with them. just dumping each successive code/query/error on a help forum isn't really you doing this thing called programming.

 

the mysql documentation contains syntax prototypes for each kind of query, that show what's required, what's optional, what order the terms go in the query, and where lists of same meaning things, separated with commas, can be used.

Edited August 27, 2015 by mac_gyver

[bambinou1980]

Thank you but this is the type of tool I was actually looking for, looks pretty good? With php I am ok now, but mysql I believe could be written far faster by using a tool

https://www.devart.com/dbforge/mysql/querybuilder/

 

Before I used to spend ages trying to work out templates for boostrap 3, now I use Layoutit.com and spend only 10 minutes per layout instead of hours and get the same result. All I want is to be more efficient with my own work by spending more time thinking about logic and less time on tiny things like this little commas, I think that having tools like mysql benchmark, layout it and others really helps, all i need now is a cool tool that writes the queries for me based on a visual implementation, I will still think about the logic of the queries but at least will not lose times on misplaced commas.

 

I will try one of those tool next week and see if it helps...I am sure it will.

[ginerjm]

Commas, semis, periods, parens, curly braces, > symbols, typos/misspellings -all these are part of that great big world we call Programming.