Searching from a database

StuA

Hello folks,

I have a database containing 24 tables of data. The data is car registration plates that are available for purchase. Each table has its own category of registration. What I need to do is set up a search from a web page where the user searches for their desired plate, it then searches all of the tables in the database for that plate and displays the plate and its price from the database if it is available, or some alternatives if it's not. Is there any easy way to do this?

Thanks for your help.

Norman_Graham

Hi StuA

This is an RFTM, but we're a generous lot:

<?php
$plateQuery=$_GET["regEntry"]; //Get this from your HTML form

$regQuery= "SELECT reg_number, price, availability FROM table_1, table_2, table 3, table_[...]
WHERE reg_number='$plateQuery' AND availability='yes'";

$regResult=mysql_query($regQuery, $connect); // you need the connection parameter here

echo $regResult; // You might want to format this a bit
?>

Obviously, you need to set up the form accordingly and you need to add your DB-connection parameters, complete the query with a full list of the tables to be queried and so on.

HTH

Norm

StuA

Thanks for your help Norm. I'm well on my way now.

A problem I have come across though is this:

Where I have my line of code for selecting from the database (WHERE reg_number='$plateQuery' ) the field 'reg_number' is actually split into three fields in the database. These are labelled 'first', 'second' and 'third', because that's how I received the data. So what I need is a way of putting the three fields together to form something that I can call 'reg_number' and then compare this to what is being submitted from the form.

Any ideas how I can do this?

Norman_Graham

Hi Stu

MySQL can do just about anything with the data in your table. The command your looking for in this case is CONCAT. As you might imagine, it is short for concatenate. The syntax is:

 SELECT CONCAT(reg1, '-', reg2, '-', reg3) AS full_reg FROM my table

In my example I've used hyphens to bind the parts together. You can use any punctuation or separation character you want, or indeed use none and allow the parts to stick together without spaces, commas or the like. I don't know what your requirements are.

You can find out more about CONCAT() by reading the MySQL manual under SELECT syntax.

HTH

Norm

RossC0

If a using a mysql database, another idea which may be worth investigating, would be to create a single table with all the data and the plate details in a single field.

Then add a fulltext index to the plate field - so then a match against search would bring back similar plates that are available as well.. Not sure how successful / applicable this would be but might be worthwhile.

Roger_Ramjet

ABSOLUTELY RossCO. That is the way to go.

StuA, there is an overhead for each table in a database, drawbacks to using many tables . It also makes your queries more complex, and slower in many circumstances.

Instead of 1 table per type = 24 tables, use one table with an additional column for type. You only need seperate tables if the diff types have very different attributes that mean totally different column counts and data types.

StuA

I've used Norm's idea (above) of concatenating. It seems to be working ok but is not displaying any results at the end. Here's the code I have used:

<?php
$server = "*********";
$user = "";
$pass = "****";
$db = "*******";

    $connect = mysql_pconnect($server, $user, $pass);

$plateQuery=$_GET["regEntry"]; //Get this from your HTML form

$regQuery= "SELECT CONCAT(first, second, third) AS full_reg FROM avail_02, avail_03, avail_04, avail_05, avail_51, avail_52, avail_53, avail_54, avail_a, avail_b, avail_c, avail_d, avail_e, avail_g, avail_h, avail_j, avail_k, avail_l, avail_m, avail_n, avail_p, avail_r, avail_s, avail_t, avail_v, avail_w, avail_x, avail_y WHERE full_reg='$plateQuery'";

$regResult=mysql_query($regQuery, $connect);

echo $regResult;

What have I done wrong? It hasn't come up with any errors, just a blank page.
?>

RossC0

try:

$connect = mysql_pconnect($server, $user, $pass);
mysql_select_db($db) or die ("Unable to select database");

$plateQuery=$_GET["regEntry"]; //Get this from your HTML form

$regQuery= "SELECT CONCAT(first, second, third) AS full_reg FROM avail_02, avail_03, avail_04, avail_05, avail_51, avail_52, avail_53, avail_54, avail_a, avail_b, avail_c, avail_d, avail_e, avail_g, avail_h, avail_j, avail_k, avail_l, avail_m, avail_n, avail_p, avail_r, avail_s, avail_t, avail_v, avail_w, avail_x, avail_y WHERE full_reg='$plateQuery'";

$regResult = mysql_query($regQuery) or die('Query failed: ' . mysql_error());
$row = mysql_fetch_array($resultSet, MYSQL_ASSOC);
print_r($row[0]);

Remember to select the a database to query against!

I'd advise before going live putting in some validation for the $plateQuery before its used in a query!

If theres an error with the sql then it should be reported!

StuA

That code went through everything fine until it got to $regResult and came up with this error:

Query failed: Column: 'first' in field list is ambiguous

What does that mean?

RossC0

ok - your query isn't clear enough for mysql.

The problem is when you say: CONCAT(first, second, third)

Mysql doesn't know what table these fields come from as they are the fieldnames for all the tables! So it throws an error...

StuA

I see what you mean.

What's the best way around that though? I'd rather not have to put all the data into one table. There are literally millions of entries across the 24 tables and it would take quite some time to merge them all into one table I imagine. Is there any coding alternative?

Roger_Ramjet

There is a work around: use a UNION query. This can be used to return a match in any table, and can even have a marker for which table it was found in. I don't know how it will perform with this many tables, you'd have to benchmark it

SELECT reg_number, price, availability, 'table_1' FROM table_1 WHERE eg_number='$plateQuery' AND availability='yes'
UNION
SELECT reg_number, price, availability, 'table_2' FROM table_2 WHERE eg_number='$plateQuery' AND availability='yes'
UNION
SELECT reg_number, price, availability, 'table_3' FROM table_3 WHERE eg_number='$plateQuery' AND availability='yes'
UNION
SELECT reg_number, price, availability, 'table_4' FROM table_4 WHERE eg_number='$plateQuery' AND availability='yes'
UNION

etc

Roger_Ramjet

PS change it to 'table_1' AS table_name FROM table_1
etc

RossC0

I'd seriously consider investigating the single table method! Its got to be simpler and faster for the search!

Roger_Ramjet

Yep. Look size does not matter that much. Even millions of records will still only require a few disk reads to locate a single records provided that the table is properly indexed. Try reading the manual about how indexes work and how to benchmark and to optimise queries.

A final alternative is using merge tables

StuA

I'm really struggling to make this work to be honest. Perhaps I structured the tables in the database in the wrong way and this hasn't helped.

Is anyone able to do some advising by email? I'm willing to pay if necessary as I need to get this finished within the next 24 hours really.

Anyone able to help please email me at webmaster@1226.net

Thank you

RossC0

Stu,

We can help here for free!

Are all your tables the same ?
What are the fields?
How many tables?
How many rows are there (on average)?
How else are they used?

RossC0

StuA

Hi RossCO,

Thank you - it's just that I've lost my rag with the thing, totally!

Here's the situation - In the database there are 24 tables, each one represents a different type of car registration (i.e. a different year of release). The majority have between 200,000 and 800,000 records in each table, though the most recently released registrations all have over 1,000,000 records, some approaching 2,000,000! You can see just how much data I'm dealing with here!! And also why I chose not to lump them all into once database!!

All the tables are structured the same, here are the field titles (first, second, third, currency, price). Examples of their content are as follows: First = AL, Second = 02, Third = GAR, Currency = £, Price = 799.

When called from the database this will display the registration AL02 GAR Price: £ 799

The tables aren't really used for anything else other than to search. Does this make things clear enough? I hope you understand because I'm struggling a little! :-)

Thanks for any help you're able to provide.

laserlight

You can see just how much data I'm dealing with here!! And also why I chose not to lump them all into once database!!

It doesnt matter if you're using 1 table or 24 tables - you're still dealing with that much data.
So it looks like it would be simpler to use a single table instead, and then search that table.

RossC0

OK - no problems,

Excuse my ignorance here, but are there differences between the plates released each year?

i.e. How do 2004 and 2005 plates differ?