Fetching Data From Multiple Tables

[aneeb]

Hey everyone! i am having some problem. In my code i am fetching data from multiple tables, but the code is having some problem it give me "mysql_fetch_array() expects parameter 1 to be resource, boolean given " error ...

 

here is my code

 

<?php
$localhost="localhost";
$dbuser="root";
$dbpass="";
$dbname="shoppro";
$connect=mysql_connect($localhost,$dbuser,$dbpass);
if ( !$connect )
{
echo "Error connecting to database.\n";
}
mysql_select_db("$dbname",$connect);
$count = "select count(*)-1 from core_store_group";
$i = '';
$sql = '';
for($i=0;$i<$count;$i++){
$sql = "select name,price,short_description,sku,weight,promotion from catalog_product_flat_'".$i."'". "UNION ALL";
}
$query=$sql.substr(0,$sql.strlen($sql)-9);
$result=mysql_query($query);
while($row=mysql_fetch_array($result)){
echo $row['name'];
}
?>

 

 

please help me..

[ManiacDan]

That error means your query failed and you didn't handle the errors. There's a number of things wrong with your queries. No spaces around UNION ALL, the loop is useless, you're using some potentially reserved keywords, and your database apparently gives root access to anyone.

[aneeb]

so what can i change in the code? can you correct the code and rewrite it? Please..

[Jessica]

See the link in my signature for the SQL error.

 

There's a lot of other stuff wrong, which Dan mentioned. The loop (I assume) he's referring to is for($i=0;$i<$count;$i++){ because $count is a string.

[Psycho]

I can't find a link to the forum rules since the site changed, but I know that one of them if that any requests to have code written for you should be posted in the freelance section. ManiacDan provided some guidance on things you need to check. So, what have you done with that information other than respond back asking for someone to do the work for you? We are here to help, but we want the person receiving the help to learn in the process.

 

So, I'll give you this. Whenever running a query I suggest always creating the query as a string variable (which you are doing) so that you can echo the query to the page for debugging purposes when there are problems (which you are not doing). If you echo'd the query to the page you would see several problems - there are syntax errors and you are not generating the query you think you are. You are overwriting $sql on each iteration of the loop - not concatenating the queries as I think you intended. But, you need to take a step back. There should be no reason to have tables such as catalog_product_flat_1, catalog_product_flat_2, catalog_product_flat_3, etc. You should have just 1 table for that data. You can add an additional field in that table to further categorize/separate the data by core_store_group if that is what you need. If you set it up correctly you only need one single query to get the information you need.

 

So, instead of fixing the code above I would highly suggest fixing your database structure first. Otherwise you are just creating more problems as you progress further.

[ManiacDan]

No, I'm not rewriting your code. You don't even appear to realize you're using PHP. IS this PHP? Lines like this:

 

$query=$sql.substr(0,$sql.strlen($sql)-9);

 

That looks like you're trying to use C# or something.

 

I said your loop is useless because it overwrites the value of $sql every time. Also, because $count isn't a number.

 

Start debugging this. Print each variable every step of the way to see what it is. Why isn't it what you expect? Research the functions you're using and what they're supposed to do (as well as how to use them).

 

Also, redesign your database so you don't have 500 tables with the exact same structure and name with numbers at the ends. If you ever do anything where you have tables or variables with sequential numbers at the end, you're doing it wrong.

Edited October 8, 2012 by ManiacDan

[Psycho]

No, I'm not rewriting your code. You don't even appear to realize you're using PHP. IS this PHP? Lines like this:

 

$query=$sql.substr(0,$sql.strlen($sql)-9);

 

That looks like you're trying to use C# or something.

 

Yeah, that's PHP, he is trying to remove the last 'UNION ALL' at the end of the concatenated query that he thinks he was building. But, yeah, completely wrong approach. There should be one table not many.

[ManiacDan]

Yeah, that's PHP, he is trying to remove the last 'UNION ALL' at the end of the concatenated query that he thinks he was building. But, yeah, completely wrong approach. There should be one table not many.

That's what I thought at first too, but look at the way he's doing it:

$string.function(arg1, arg2);

 

It looks like he's trying to chain the substr() function off the string object. WE think it's concatenation because we're well versed in PHP, but in 15 other languages that would be valid object-function access.

 

[aneeb]

I am using magento framework, and the table name structure are like this catalog_product_flat_1 (for store 1). I want to get data from all these tables and display them into my website. That's what i wanted to do..

Edited October 8, 2012 by aneeb

[Psycho]

. . . the table name structure are like this catalog_product_flat_1 (for store 1). I want to get data from all these tables and display them into my website.

 

Which is exactly what you should NOT be doing. You should not create multiple tables to differentiate data between stores! You should have ONE table for all the products and have an additional column to identify which store each product is associated with. What you are trying to do will only add complexity and cause you to run into more issues as you try to develop further features.

 

Your core_store_group table should have fields such as:

store_id, store_name, etc.

 

Then you should have one table for catalog_product_flat with all the same fields you have now, but it should have one additional field for store_id

 

Basically, we can't (or won't) help you with the current database structure.

[Psycho]

It looks like he's trying to chain the substr() function off the string object.

 

You're right. I get PHP and Javascript formats confused some time and mistook the substr() function as valid PHP.

[Barand]

Dan and Psycho,

 

Have you considered that this guy may have no control whatsoever over the database structure he has to work with?

[ManiacDan]

Unfortunately you're in a situation where literally everything is wrong. Sometimes, when you crash a car, it's easier to just buy a new one rather than fix the old one. Your car is totaled.

[Psycho]

Dan and Psycho,

 

Have you considered that this guy may have no control whatsoever over the database structure he has to work with?

 

No, because this would fall into one of four scenarios:

 

1. He knows this is a bad configuration, and he has control over it. In this case he is a moron and doesn't deserve our help.

 

2. He knows this is a bad configuration, and he doesn't have control over it. In this case he should have stated that fact since he does no better. So, I doubt this is likely. And he probably still doesn't deserve our help for not providing that information, thereby wasting our time.

 

3. He doesn't know this is a bad configuration, and he has control over it. This is the most likely case. I see people doing these types of things all the time when they start using databases. Which is what prompted my, and probably ManiacDan's, responses

 

4. He doesn't know this is a bad configuration, and he doesn't have control over it. This is a possibility. But, I think we are still right to point out the major flaw in the design. Maybe I was a little harsh in my response that

Basically, we can't (or won't) help you with the current database structure.

 

But I still think that scenario #4 is very unlikely. If the OP can provide a statement otherwise then I might be willing to help. But, I have seen too many people post crappy logic/code/processes asking for help with a specific piece. Then when someone responds that they are taking the wrong approach they get pissy and say they just need help with that one thing.

 

I'm not trying to be the over ambitious hall monitor for what is considered "correct" code. But, I think it is relevant of us to push people in the right directions when it is obvious they are taking approaches that are fraught with problems. Else we will be helping them the next day on another problem that could have been avoided if they did it correctly, and then the next day, . . .

 

As I said before, I see people new to databases trying to do similar things all the time (even I did in the beginning). I only wish I had someone helping me to avoid those mistakes early on.

Edited October 9, 2012 by Psycho