query works on local but not on hosting server :/

[garry]

So I'm using this query to get review information from a database:

 

	$query ="
	SELECT *
	FROM artists, albums, reviews, users
	WHERE reviews.id = '$id'
	AND reviews.artist_id = artists.id	  
	AND albums.id = reviews.album_id
	AND users.id = albums.user_id

		";

 

Now this works fine on my local server but it does not work on my hosting server. It is because the query is not obtaining any results on the hosting server, however it always gets the result on my local.

 

And also, if i remove the "AND users.id = albums.user_id" part from my query, it will show up fine on my hosting server, but the user gets messed up. But apart from this, everything else works. I don't understand why it's doing it :/

 

Can anyone help?

[samshel]

Hi

 

Just a double check. you sure the data is same on both servers?

 

try breaking the query. You have joined 4 tables, try with only 2 tables that are having issues.

 

 

[garry]

Files and databases are the same on both servers. I did break the query by removing the users part, and that made the query work (but with the user part messed up).

[samshel]

Try joinin album and user table

[garry]

What do you mean by "joining"?

 

[samshel]

$query ="
	SELECT *
	FROM albums, users
	WHERE users.id = albums.user_id

		";

[garry]

Nope, using that still gives me a result on local but nothing on hosting server. It must be something to do with users table..

[samshel]

yes, please confirm if structure and data of users table is same on both servers.

[garry]

They're definitely the same. I even dumped the sql of my local servers DB and re-imported into the hosting servers one. They're definitely exactly the same

[samshel]

sorry i cant comment further without actually seeing the tables just make sure the problem is with this query only, if it is its got something to do with structure and data, or it may turn out to be a problems some where else you never know. Best of Luck !

[garry]

Does anyone else have any ideas? I can provide any information you might require?

[Darklink]

Heh strange way of relating tables. Try doing LEFT JOIN instead. Maybe the different version of MySQL on the hosted server doesn't like using your method.

 

Example:

<?php
$sql = "SELECT t1.*,t2.* FROM table1 t1
          LEFT JOIN table2 t2 on(t2.other_id=t1.id)
          WHERE t1.somefield = 'something'";
?>

[Zane]

So on the local server it gets results

and

on the hosting server it gets NO results...

 

you claim it's the exact same data and structure...

 

 

Are there any errors that appear whatsoever?

[garry]

Well I was never taught any other way of relating the tables. I've heard of joins, but never used them. Would you be able to explain to me what exactly they are and why they'd be better than what I'm currently using?

 

And no errors occur because I use an: if(mysql_num_rows($result) > 1) {

[garry]

Okay, so I think I've found the problem. On my local server, the auto increment for my users.id starts from "0", whereas on my hosting server it starts from "1". Therefore, as the user who creates everything so far has had a userid of "0", this exists on my local but not on the hosting.

 

Does anybody know how I can make the users table start from 0 on my hosting (using phpmyadmin) instead of 1?!

[garry]

Anyone?

[Zane]

UPDATE users SET id = id - 1;

 

put that into the SQL tab on phpmyadmin

[garry]

That does fix it, thank you. But I wanted to know why it's adding one in the first place? I don't really want to be needing to run that query every time i import the database. I can give you any information you want about the table if you like?

 

Thanks!

[garry]

Can anyone help me with this?

[Buddski]

A little thing Id like to point out, im not sure if you meant it but..

if(mysql_num_rows($result) > 1) {

will only display results if there are 2 or more records found.

[garry]

A little thing Id like to point out, im not sure if you meant it but..

if(mysql_num_rows($result) > 1) {

will only display results if there are 2 or more records found.

Oh no, I have 0 on my actual php file but that was just an error I made when quickly typing it out. Thanks anyway!

[garry]

Still nobody..?