HELP ME OUT!! (query multiple tables..)

[Anon]

I'm really in need of a coding help. (I'm not much of a coder myself).

Could some kind person please write up a sample code (for mysql) that does the following.

1)
Perform a search on 12 separate tables for a specific entry on a certain row.
(each table has exact same features, name them table1, table2,...etc)
For example: All of the tables have the row "cars" and I need to search for all tables for entry 'ferrari' that's recorded in the row "cars"

2)
Enter all the rows that contain 'ferrari' into a new table. (say table13)

3)
then display the result of table13 (I think I can do this one myself)

4) Finally, is it possible to show a text line like "searching database... Please wait" while the above 3 operations are taking place?

Thanks to whoever that can help me out!

[andreas]

There might be som parse errors or other stupid things cuz i didnt test it... but anyway... you can not have a "searching" message... ok... hope this works..

BTW I didnt write the SELECT thing from the table13 database, since you said you'd probably be able to do that yourself

Andreas

/ Connect to the database and do that kinda thing here /

$search = 'ferrari';

for($i = 1; $i < 13; $i++)
{

$result = mysql_query("SELECT * FROM table$i WHERE car = '$search'");
while($stuff = mysql_fetch_row($result))
{
	$query = "INSERT INTO table13 VALUES(";
	for($j = 0; $j < mysql_num_fields($result); $j++)
	{
		if($j != 0)
			$query .= ", ";
		$query .= "'".$stuff[$j]."'";
	}
	$query .= ");
	mysql_query($query);
}

}

/ Do the select from table13 here /

[mp96brbj]

If you have the option of redesigning the database, I would strongly recommend the following:

If all your tables 1-12 have the same columns, why not put them all into one single table, and add an INT column called 'table_number' or something to differentiate the rows.

This would make selecting and adding much easier, and you might be able to solve your other problems on your own.

Let's say your current design looks like this:

Table_1:

id cars

1 ferrari
2 volvo
3 ford

Table_2:

id cars

1 saab
2 ferrari

Imagine merging it into this instead:

Table_all:

id table_number cars

1 1 ferrari
2 1 volvo
3 1 ford
4 2 saab
5 2 ferrari

I also believe that this would increase the speed of your queries, since only one table would have to be read. You might also want to add an index on table_number:
CREATE INDEX table_number ON Table_all (table_number);

[Anon]

Thanks A LOT LOT LTO LTO for the help guys.

[Anon]

I got simular problem, i need to search different tables for one colum (author).
I have working version for each table. SQL that i'm using is:

SELECT author, count(*) as total FROM $ForumTableName GROUP BY 1 ORDER BY 2 DESC LIMIT 10.

All my tables got different names, but all those names are stored in one separate table. I need the script count all authors in all tables and output it in a table(i can do table by myself)

Your script doesn't work for me, but i think they shouldn't be far apart, can you please show me how to do it.

Vulpes

[Anon]

Hi,
Thanks for your suggestion on the forum (adding an extra column as 'tablenumber)

I think it might work for what I need, however, as a php newbie.. could you help out somemore?

say if I need to delete certain row, I used to use

<? mysql_query("Delete FROM table1 where id = $id", $db); ?>

If this specific row is recorded as for table 1, with your new addition of the column, how should I change the line of code to?

---

Also, this line is used to record the data entered:
mysql_query("update $table set date = '$date' where id = '$id'");

how should I change it to?

Finally, I mentioned that I wanted to use 12 tables to record 12 different car manufacturers. I wanted to use 12 php pages so when a person clicks on ferrari.php, he will be brought to a page with the $table = ferrari, If he clicks on audi.php, he'll be brought to a page with the $table = audi...and so on..

This is long and tedious with repeating codes,only change is the table name.

So, If I take your suggestion, which I most likely will, is there any way to simply use 1 php page and somehow manipulate the links on the main page so that when you click on a different link, the constant $table can be changed.

Thanks a trillion for your time!!

[Anon]

yihung, shue you can do that, you saw websites using filename.php?num=something&id=something ?
well, you can create php file called cars.php, then for table name put $manufacturer and put all what you need there, and then you call script like that:
cars.php?manufacturer=ferrari
it will go to the table named ferrari and do whatever you want it to do 🙂
but, i read somewhere that if hackers can find the table name for your MySQL database and if you have some kind of form, you can get in troble big time, so be careful.

Vulpes

[Anon]

Vulpes, thanks for the reply..

so is there a way to cloak the url?

[Anon]

yes, it's not actually cloaking, but for the hacker it's going to be harder to find your table names.
add that somewhere to your cars.php:
---code---
$carcloak = array("1" => "ferrari", "2" => "porche", "3" => "bmw");
---code---
1 is going to be in address line ie cars.php?manufacturer=1 which is going to mean ferrari(table name), 2 is porche table name, etc.
now in your
select * from $manufacturer

change $manufacturer to $carcloak["$manufacturer"]

that's it!

Vulpes

[Anon]

really appreciate your help! 🙂

here's another newbie question.

Now that I added table_number for my table (gonna just use 1 talbe and use table_number to differentiate the groups. ie. ferrari, porsche..etc)

1)
why do I need to index it, what's the meaning of it.

2)
I started making it so that my 'add record' script can add in table_number (user can choose whether to set that entry to table 1, 2 , 3 ...etc (where 1 can stand for ferrari or other manufacturers)

3)
now im stuck. How do I show the list so that it ONLY shows those with table_number = 1?

here's my function:

$result = mysql_query("SELECT * FROM table order by name", $db);

listingfunctin();

(this one lists everything in the table, but i want it to list those with table_number = 1 in the table.)

---

really appreciate the help!

[Anon]

this solution I found out while I played around with the code for 3) (I started learning php 2 days ago..please bear with me 🙂 )

$result = mysql_query("SELECT * FROM $table WHERE table_number = '$listtable'", $db);

so in my URL, i just enter car.php3?action=list&listtable=1

where action and list is my previous function, and 1 is just previously set table 1 within the big table =) that worked.

[mp96brbj]

Goodie, that's exactly how I would have solved it myself!

As for you previous question about indexes: they are used to create an "overview" of the table, so MySQL can search it more quickly. Since your queries in this case would select WHERE table_number = '$something', you could add an index to table_number to speed it up.

Further reading about indexes:
http://www.phpbuilder.com/columns/tim20010110.php3
http://www.mysql.com/doc/I/n/Indexes.html
http://www.mysql.com/doc/E/X/EXPLAIN.html

[Anon]

I have encountered another big problem...

I have a car.php file that stores all functions, and a template.inc that stores how the pages look.

Now, in the car.php, a line is used to call template.inc : require "template.inc"

Here comes the problem. When I make those 12 pages for each car manufacturers, I need to make 12 templates. so it would be like ferrari.inc, audi.inc.. or template1.inc, template2.inc..etc

When I call the pages, I need to call car.php but not those indicidual templates... How then can I simplify it so that I dont need to make another 11 car.php (with different name)?

I tried this:

if (ref == '1')
{
$template = ferrari.inc;
}
require "$template";

But that doesnt work. (btw, if it works, how do I add that ref = 1 in my link to call it? 🙁

Thanks.

[vulpes]

if you use my method,
Try that:
if (ref == $manufacturer) {
$template = $carcloak[$manufacturer].inc; /real name i.e name of the table required. /
}
require "$template";

that way you going to need only one if statment for all you pages.

Vulpes

[Anon]

I'm really sorry, I woke up with a huge headache.. and didn't quite get how your codes works. Let me explain my situation now.

I have 1 huge table, and I use table_number (an index) to differentiate between small groups.

So when I wanna call whatever that's recorded in table_number 1, I use:

car.php?action=sortmodel&table_number=1

---

Now I have 1 car.php and 1 template.inc which stores how each page would look like.

what should I do so that when I use a different URL, the car.php can require different templates?

Vulpes, I think I kinda get your code but that's for multiple tables right? But after some thoughts, I decided to take Bjorn's idea and put everthing in 1 table (spent a night changing the code for that...).

Thanks

btw, this is probably the most helpful forum I have been to 🙂

[mp96brbj]

One quick 'n easy way would be to name the .inc files with numbers, so "1.inc" would be the Ferrari template.

require($table_number.".inc");

However, I understand if you want to keep the file names easy to understand. I believe Vulpes was suggesting that you create an array like this:

$carcloak = array(
1 => 'ferrari',
2 => 'audi',
3 => 'ford'
);

That way, $carcloak[$table_number] would result in 'ferrari' if $table_number was 1.

As you write more and more scripts for your site, you might need to create that array with manufacturers in several files. That can be difficult to maintain, so it's desirable to store that info in one place only. You could put the mentioned array in a script that is included on all your pages, but I'd like to suggest that you create another MySQL table for this instead. You could even add a column with the template's file name:

Table manufacturers:

id manufacturer template

1 Ferrari ferrari.inc
2 Audi audi.inc
3 Ford ford.inc

In this table, the "id" would be relative to "table_name" on your other table (the big one), so you can join these tables in the future if you want to make more complex SQL queries.

Congratulations, you've just created a relational database! ^_
http://www.phpbuilder.com/columns/barry20000731.php3

Hope I didn't confuse you with all my suggestions. ^_

[Anon]

Sounds great! (I'll try that when I get the time 🙂 )

One important thing you forgot to mention is How do I refer to different templates in the URL link?

say in my main page I got links to 12 manufacturers, ferrari, audi..bmw..etc.

what should each one look like. say ferrari is 1.inc .

is it something like car.php?table_number=1

and if I want to add other actions such as sorting the table, do I do it like:

car.php?table_number=1&action=sortmodel ?

When should I use '&' and when to use '?' ?

(as you can see, I'm a real newbie 🙁 )

Anyways, one more question :p

I'll need another small groups for country specific groups. so 1 to 12.inc, I use table_number to refer to, 13 to 15 will depend on the code number of a new index called country_code . Can I somehow add that to the if statement you suggested? And how would I call it from the URL?

Thanks a billion!

Yihung

[Anon]

oops, I'm always quick with the questions.. I got the answer to my first question..with a bit of trouble on the array thing.

Here's my code:

$carcloak = array(1 => 'ferrai', 2 => 'porche', 3 => 'bmw');

require($page.".inc");

---

so in the url, I tested

car.php3?page=ferrari&action=sortmodel

This worked!! BUT

car.php3?page=1&action=sortmodel

Does Not work and here 's the error msg:

Fatal error: Failed opening required '1.inc' in ......

I guess that array didn't successfully translate the constants I wanted.

---

btw. I really like programing and would take time to read more materials instead of just shooting questions here 🙂 However, I'm one of those ppl that don't have the time to learn it and really need to get the script working 🙁 So I guess I'm learning as I ask 🙂 Thanks for the help again. Can't express how thankful I am.

[mp96brbj]

Yes, I reckoned you could be using $table_number for both selecting the right rows from the DB and to find the template filename.

The query string begins with a ? after the filename, and every variable/value pair thereafter is separated with &. So always begin with ? and then use &.

[mp96brbj]

You forgot to actually make use of the array $carcloak. This should work with car.php3?page=1&action=sortmodel :

Btw, you had a typo in the word 'ferrari':

$carcloak = array(1 => 'ferrai', 2 => 'porche', 3 => 'bmw');

require($carcloak[$page].".inc");