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

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.