garry Posted June 3, 2008 Share Posted June 3, 2008 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? Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/ Share on other sites More sharing options...
samshel Posted June 3, 2008 Share Posted June 3, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-556391 Share on other sites More sharing options...
garry Posted June 3, 2008 Author Share Posted June 3, 2008 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). Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-556397 Share on other sites More sharing options...
samshel Posted June 3, 2008 Share Posted June 3, 2008 Try joinin album and user table Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-556402 Share on other sites More sharing options...
garry Posted June 3, 2008 Author Share Posted June 3, 2008 What do you mean by "joining"? Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-556405 Share on other sites More sharing options...
samshel Posted June 3, 2008 Share Posted June 3, 2008 $query =" SELECT * FROM albums, users WHERE users.id = albums.user_id "; Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-556408 Share on other sites More sharing options...
garry Posted June 3, 2008 Author Share Posted June 3, 2008 Nope, using that still gives me a result on local but nothing on hosting server. It must be something to do with users table.. Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-556413 Share on other sites More sharing options...
samshel Posted June 3, 2008 Share Posted June 3, 2008 yes, please confirm if structure and data of users table is same on both servers. Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-556414 Share on other sites More sharing options...
garry Posted June 3, 2008 Author Share Posted June 3, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-556415 Share on other sites More sharing options...
samshel Posted June 3, 2008 Share Posted June 3, 2008 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 ! Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-556422 Share on other sites More sharing options...
garry Posted June 4, 2008 Author Share Posted June 4, 2008 Does anyone else have any ideas? I can provide any information you might require? Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-557190 Share on other sites More sharing options...
Darklink Posted June 4, 2008 Share Posted June 4, 2008 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'"; ?> Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-557238 Share on other sites More sharing options...
Zane Posted June 4, 2008 Share Posted June 4, 2008 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? Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-557243 Share on other sites More sharing options...
garry Posted June 4, 2008 Author Share Posted June 4, 2008 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) { Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-557250 Share on other sites More sharing options...
garry Posted June 4, 2008 Author Share Posted June 4, 2008 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?! Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-557256 Share on other sites More sharing options...
garry Posted June 4, 2008 Author Share Posted June 4, 2008 Anyone? Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-557341 Share on other sites More sharing options...
Zane Posted June 4, 2008 Share Posted June 4, 2008 UPDATE users SET id = id - 1; put that into the SQL tab on phpmyadmin Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-557965 Share on other sites More sharing options...
garry Posted June 5, 2008 Author Share Posted June 5, 2008 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! Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-558096 Share on other sites More sharing options...
garry Posted June 6, 2008 Author Share Posted June 6, 2008 Can anyone help me with this? Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-558969 Share on other sites More sharing options...
Buddski Posted June 6, 2008 Share Posted June 6, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-558976 Share on other sites More sharing options...
garry Posted June 6, 2008 Author Share Posted June 6, 2008 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! Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-559018 Share on other sites More sharing options...
garry Posted June 7, 2008 Author Share Posted June 7, 2008 Still nobody..? Quote Link to comment https://forums.phpfreaks.com/topic/108517-query-works-on-local-but-not-on-hosting-server/#findComment-560117 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.