Page 1 of 1

Query problem

Posted: Thu Sep 03, 2009 8:46 am
by synical21

Code: Select all

<?php 
 
# connect to the database
mysql_connect(*****);
mysql_select_db('*****');
 
 
$id = (isset($_GET['id']) && !empty($_GET['id']) && is_numeric($_GET['id'])) ? $_GET['id'] : 0;
 
$sql = "SELECT * FROM `proof`,`users` WHERE users.id = $id and proof.userproof_id = $id UPDATE users set user_money = user_money + perperson WHERE users.id = $id" or die( mysql_error() );
?>
 
This obviously doesnt work as i cant join the tables proof/users correctly. Ive tried a few ways now and im getting no where

the perperson is the intergers to add to the overall money but perperson is in table proof. Hence why im trying to join them. Then the WHERE part just secures the money goes to the right user as both should = $id.

I feel ive got the right idea but just can not execute it. Any advice would be great.

Re: Query problem

Posted: Thu Sep 03, 2009 9:43 am
by Mark Baker
As far as I'm aware, you can't do a SELECT and an UPDATE in the same SQL statement. You'd need to use two separate statements: one for the UPDATE and a second for the SELECT.

Code: Select all

 
$sql = "UPDATE users 
   SET user_money = user_money + perperson 
WHERE id = $id";
 

Code: Select all

 
$sql = "SELECT `proof`.*,
       `users`.* 
  FROM `proof`,`users` 
 WHERE users.id = $id 
   AND proof.userproof_id = $id";
 

Re: Query problem

Posted: Thu Sep 03, 2009 10:15 am
by synical21
O really, ill try that method and seperate it now.

Re: Query problem

Posted: Thu Sep 03, 2009 10:23 am
by synical21
Hmm maybe im coding this wrong in the query as when i try withdraw data from one of the tables just to see if information is being selected it displays nothing. Aswell as no calculations are done. I need to break up the two queries but i still think im writing the actual queries wrong.

Re: Query problem

Posted: Thu Sep 03, 2009 10:26 am
by Ollie Saunders
You can test the queries in isolation with something like phpMyAdmin. Also consider testing the return value from mysql_query(), it should be a resource, not false. A call to mysql_error() will return a string explaining what went wrong last.

Re: Query problem

Posted: Thu Sep 03, 2009 10:28 am
by synical21
Ok ill log on to phpmyadmin now and give it a go the select query worked ... Aha in the update query this goes wrong

#1054 - Unknown column 'perperson' in 'field list' i also tried
#1054 - Unknown column 'proof.perperson' in 'field list'

why doesnt the update understand where perperson is when the table proof is selected :S

The code starts as UPDATE users so maybe it just gets all the information from users and not proof table to so it doesnt understand perperson field. So the question is how can i make the update query understand another table kinda thing

Re: Query problem

Posted: Thu Sep 03, 2009 11:00 am
by synical21
fixed it

changed the update to


UPDATE users, proof
SET user_money = user_money + perperson
WHERE id = 59;

and it works.... Little push in the right direction and i fixed it my self thank you.

Re: Query problem

Posted: Thu Sep 03, 2009 11:19 am
by Ollie Saunders
I knew you would.

Re: Query problem

Posted: Thu Sep 03, 2009 11:46 am
by synical21
Just tried to put that query into php for about 20min now and its not working, but im not getting any errors from the page.

This is the query in phpmyadmin what works
SELECT `proof`.*,
`users`.*
FROM `proof`,`users`
WHERE users.id = 59
AND proof.userproof_id = 59;

UPDATE users, proof
SET user_money = user_money + perperson
WHERE id = 59;

UPDATE users
SET jobs_completed = + 1
WHERE id = 59;
Now i try put that in the script

Code: Select all

<?
$id = (isset($_GET['id']) && !empty($_GET['id']) && is_numeric($_GET['id'])) ? $_GET['id'] : 0;
 
 $sql = mysql_query("SELECT `proof`.*,
       `users`.*
  FROM `proof`,`users`
 WHERE users.id = $id
   AND proof.userproof_id = $id");
 
$sql = mysql_query("UPDATE `users`,`proof`
   SET user_money = user_money + perperson
 WHERE id = $id");
 
 
$sql = mysql_query("UPDATE `users`
   SET jobs_completed = + 1
 WHERE id = $id");
 
 
 
?>
Am i doing something really stupid?

Re: Query problem

Posted: Thu Sep 03, 2009 3:24 pm
by Mark Baker
Assuming you are establishing a connection to the database, and simply haven't shown that block of code.

Code: Select all

 
 $sql = "SELECT `proof`.*,
       `users`.*
  FROM `proof`,`users`
 WHERE users.id = $id
   AND proof.userproof_id = $id";
$result = mysql_query($sql) 
    or die('Invalid query: ' . $sql . ' - Error is ' . mysql_error());
 
$sql = "UPDATE `users`,`proof`
   SET user_money = user_money + perperson
 WHERE id = $id";
$result = mysql_query($sql) 
    or die('Invalid query: ' . $sql . ' - Error is ' . mysql_error());
 
 
$sql = "UPDATE `users`
   SET jobs_completed = + 1
 WHERE id = $id";
$result = mysql_query($sql) 
    or die('Invalid query: ' . $sql . ' - Error is ' . mysql_error());
 
You probably also want to loop through the result set echoing the results of the select as well.

Re: Query problem

Posted: Thu Sep 03, 2009 3:38 pm
by synical21
ill give that a go now, and yes there is a connection to the database :P

Re: Query problem

Posted: Thu Sep 03, 2009 6:48 pm
by Ollie Saunders
You're making queries but you're not doing anything with the resource returned. Walk through the steps on a basic tutorial for querying and showing results from a query, in PHP using MySQL.