[SOLVED] pullling values out of a successful SQL query

[amites]

quick question,

 

any idea why a query would provide results but when run through mysql_fetch_assoc tusn into a boolean(false) statement as though it had not retrieved any results?

 

my query looks like:

 

		$query = "UPDATE bil_msg_sent AS s"
		. "\n JOIN bil_msg_look AS l ON (l.locid = s.locid)"
		. "\n JOIN bil_users AS u ON (u.id = s.userid)"
		. "\n JOIN bil_location AS loc ON (l.locid = loc.id)"
		. "\n SET s.last_read = now()"
		. "\n , s.read_cnt = s.read_cnt + 1"
		. "\n WHERE s.msg_date BETWEEN l.look_date - INTERVAL 1 hour AND l.look_date + INTERVAL 1 hour"
		. "\n AND l.id = '" . $id_num . "'"
		. "\n AND s.active = 1"
		. "\n AND l.active = 1"
		;

 

when I var_dump the results I get

 

resource(5) of type (mysql result)

 

 

 

After I run it through mysql_fetch_assoc = bool(false)

[rhodesa]

UPDATEs don't return any records. Once mysql_query is run, the command has been executed. If you are looking to get the info again to confirm, you will need to run a separate SELECT statement. If you are looking for the number of rows updated, check out mysql_affected_rows()

[amites]

oops,

 

code looks like this, that was a separate query

 

	$query = "SELECT s.id AS msg_id, s.message AS msg, UNIX_TIMESTAMP(s.msg_date) AS msg_date, u.name AS usr, u.id AS sender_id, loc.name AS loc_name, loc.description AS loc_desc"
	. "\n FROM bil_msg_sent AS s"
	. "\n JOIN bil_msg_look AS l ON (l.locid = s.locid)"
	. "\n JOIN bil_users AS u ON (u.id = s.userid)"
	. "\n JOIN bil_location AS loc ON (l.locid = loc.id)"
	. "\n WHERE s.msg_date BETWEEN l.look_date - INTERVAL 1 hour AND l.look_date + INTERVAL 1 hour"
	. "\n AND l.id = '" . $id_num . "'"
	. "\n AND s.active = 1"
	. "\n AND l.active = 1"
	. "\n ORDER BY s.msg_date ASC";

[rhodesa]

Well...if it's returning a result, the query is not failing, but if the mysql_fetch_* functions are returning false (with no errors), then your query is returning an empty set (ie no rows matched).

 

 

Just a note: I don't think it matters, but I would get those \n's outta there.

[amites]

logically I agree with what you are saying,

 

though I've never seen a situation like this and it is bugging me to not understand it

 

I keep tinkering with different spots and keep getting the same return

 

var_dump($result):

 

resource(5) of type (mysql result)

 

 

mysql_fetch_*($result):

 

bool(false)

 

 

 

any mySQL pro's that can give me some insight or point me towards an answer?

[rhodesa]

After you set the value of $query, do an echo $query. Copy/paste the echoed value into phpMyAdmin and see what it says. I'm betting it will return "0 rows found". In this case, your SQL Query Logic is wrong. And, without knowing a bunch more about the structure of all the tables and what you are trying to accomplish, no one else will be able to fix the query.

 

My suggestion, slim the query down to a simple SELECT with no JOINs. Then slowly build on it, making sure each time you are getting your expected results.

[amites]

thank you for the infusion of common sense

 

:)

 

haven't got it working but given some debugging time I'll get it