How to search data from multiple table in mysql using php

Hi guys I’m new to this site and i really need a professional help out here. I am trying to make a search form in my page and basically i can search my database already but i need to revised the code because the process of my search form depends on a dropdown menu
which contains the table names in my database basically the searching will depend to the selected service form the dropdown menu. what i want to happen is to** eliminate the dropdown menu and make an automatic searching throughout my tables in the database and fetch the necessary data.. thanks in advance
**
this is the searchform.html

Expand|Select|Wrap|Line Numbers

 
    <html>

    <body bgcolor="#CCCCCC">

    <form method="post" action="searchresult.php" name="form2">

    <p><img src="images/europcar.jpg" width="560" height="162" /></p>

    <p align="center">Please dont live any blank space while searching</p>

    <p align="center">Search Reservation Number </p>

    <p align="center">

    <input type="text" name="search" size=25 maxlength=25 >

    <select name="table" size="1">

    <option selected="selected"></option>

    <option value="sd">SelfDrive</option>

    <option value="sdti">SelfDrive texas</option>

    <option value="ch">Carhire</option>

    <option value="chti">Carhire texas</option>

    <option value="cd">Chauffeur Drive</option>

    <option value="cdti">Chauffeur Drive texas</option>

    <option value="pudo">Pick up Drop off</option>

    <option value="pudoti">Pick up Drop off texas</option>

    </select>

    <input type="Submit" name="Submit" value="Submit">

    </p>

    </form>

    </body>

    </html>

Expand|Select|Wrap|Line Numbers

 
    **this part is the searching

    searchresult.php**

    <?php

    $table=$_POST['table'];

    $search=$_POST['search'];

    if ($search == "" or $table=="")

    {

    echo"<a href='a.html'> back </a>";

    echo "<p>You forgot to enter the reservation number";

    exit;

    }

    $dbhost = 'localhost';

    $dbuser = 'europcar_msic';

    $dbpass = 'msictran';

    $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

    $dbname = 'europcar_msic';

    mysql_select_db($dbname);

    if ($table=="cd")

    {

    $sql = mysql_query("select * from cd where Rnum like '$search'");

    }

    if ($table=="sd")

    {

    $sql = mysql_query("select * from sd where Rnum like '$search'");

    }

    if ($table=="ch")

    {

    $sql = mysql_query("select * from ch where Rnum like '$search'");

    }

    if ($table=="pudo")

    {

    $sql = mysql_query("select * from pudo where Rnum like '$search'");

    }

    if ($table=="sdti")

    {

    $sql = mysql_query("select * from sdtexas where Rnum like '$search'");

    }

    if ($table=="chti")

    {

    $sql = mysql_query("select * from chtexas where Rnum like '$search'");

    }

    if ($table=="cdti")

    {

    $sql = mysql_query("select * from cdtexas where Rnum like '$search'");

    }

    if ($table=="pudoti")

    {

    $sql = mysql_query("select * from pudotexas where Rnum like '$search'");

    }

    while ($row=mysql_fetch_array($sql))

    {

    echo " <table width='100%' border='0' align='center' cellpadding='0' cellspacing='0' bgcolor='#CCCCCC' > ";

    echo " <tr bgcolor='green'>";

    echo " <td width='5%' align='center' height='10' ><p><font color='white'><strong><B>Reservation Number </th>";

    echo "<td width='5%' align='center' height='10' ><p><font color='white'><b>Name</th>";

    echo "<td width='5%' align='center' height='10' ><p><font color='white'><b>Vehicle Type</th>";

    echo "<td width='5%' align='center' height='10' ><p><font color='white'><b>Origin</th>";

    echo "<td width='5%' align='center' height='10' ><p><font color='white'><b>Pick up date</th>";

    echo "<td width='5%' align='center' height='10' ><p><font color='white'><b>Time</th>";

    echo "<td width='5%' align='center' height='10' ><p><font color='white'><b>Destination</th>";

    echo "<td width='5%' align='center' height='10' ><p><font color='white'><b>Return Date</th>";

    echo "<td width='5%' align='center' height='10' ><p><font color='white'><b> Return Time</th>";

    echo "<td width='5%' align='center' height='10' ><p><font color='white'><b> Contact Number</th>";

    echo "<td width='5%' align='center' height='10' ><p><font color='white'><b> Address</th>";

    echo "</tr>";

    echo"<tr>";

    echo"<a href='a.html'> back </a>";

    echo"<tr>";

    echo "<td width='5%' align='center' height='10' > $row[Rnum]</td>";

    echo "<td width='5%' align='center' height='10' >$row[Fname] $row[Lname]</td>";

    echo "<td width='5%' align='center' height='10' >$row[Vehicle]</td>";

    echo "<td width='5%' align='center' height='10' >$row[Origin]</td>";

    echo "<td width='5%' align='center' height='10' >$row[Pickday] $row[Pickmonth] </td>";

    echo "<td width='5%' align='center' height='10' >$row[Pickhour] : $row[Pickmin]</td>";

    echo "<td width='5%' align='center' height='10' >$row[Destination]</td>";

    echo "<td width='5%' align='center' height='10' >$row[Rday] $row[Rmonth] </td>";

    echo "<td width='5%' align='center' height='10' >$row[Rhour] : $row[Rmin]</td>";

    echo "<td width='5%' align='center' height='10' >$row[Contact]</td>";

    echo "<td width='5%' align='center' height='10' >$row[Zip],$row[Address],$row[City] $row[Country]</td>";

    echo '<br/><br/>';

    }

    //This counts the number or results - and if there wasn't any it gives them a little message explaining that

    $anymatches=mysql_num_rows($sql);

    if ($anymatches == 0)

    {

    echo"<a href='searchform.html'> back </a>";

    echo "Sorry,Invalid Reservation Number or you are looking at the wrong place double check the number and search it again";

    }

    {

    }

    ?>

May 17 '12 #1

Subscribe Post Reply

13414

Rabbit

12,516

Expert Mod 8TB

Use a UNION ALL to combine all your different queries into one. However, you should think about normalizing your data instead of having it spread out over so many tables.

May 17 '12 #2

ehpratah

hi rabbit

im having trouble using union can you give me an example code that is running because when i try to making sample search using UNION theirs an error saying

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\msicrevised\searcherresults.php on line 32

Expand|Select|Wrap|Line Numbers

 Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\msicrevised\searcherresults.php on line 68
 

this is my codes

Expand|Select|Wrap|Line Numbers

 $sql = mysql_query("select * from sd where Rnum like '$search'; UNION select * from sdtexas where Rnum like '$search'; UNION select * from pudo where Rnum like '$search'") ;
 

am i doing it correctly?

May 18 '12 #3

Rabbit

12,516

Expert Mod 8TB

Here's an example of union.

Expand|Select|Wrap|Line Numbers

 SELECT * FROM Table1

UNION ALL

SELECT * FROM Table2

May 18 '12 #4

by: LRW | last post by:

Well, I have another question that's hard to ask, so I may end up over explaining the question. I have about 40 tables, all with a similar column structure. I want to be able to search through...

PHP

Problem decrypting data stored in MySQL using mcrypt

by: M Wells | last post by:

Hi All, I'm trying to implement encryption on certain data fields in my MySQL database and I'm experiencing ongoing problems. I seem to be able to encrypt the data without issues, but can't...

PHP

multiple table delete query error

by: Ray | last post by:

Hello all, I am attempting to delete multiple rows from multiple tables as follows: DELETE FROM attachments,responses,response_lines WHERE attachments.id IN(2,7,11) AND...

MySQL Database

Multiple Table Query using Sum Function

by: Scott Cannon | last post by:

I am trying to query 3 tables all related by Clinet_ID. The Clients table, Monthly_Expenses table and Monthly_Income table. Each client can have 0>M instances of expenses, past due expenses, and...

Microsoft Access / VBA

Search for Multiple keywords in multiple fields

by: JP SIngh | last post by:

Hi All This is a complicated one, not for the faint hearted :) :) :) Please help if you can how to achieve this search. We have a freetext search entry box to allow users to search the...

ASP / Active Server Pages

How can I get data on database MYSQL using javascript function?

by: ariel gons | last post by:

This is one of my project and also my thesis in school. How can I get data on database MYSQL using javascript function? Is there anybody here can help me with this problem.. Can anyone give...

Javascript

How To Dynamically Search A Database Table

by: Merlin1857 | last post by:

How to search multiple fields using ASP A major issue for me when I first started writing in VB Script was constructing the ability to search a table using multiple field input from a form and...

ASP / Active Server Pages

Can't Search data from table

by: billa856 | last post by:

Hi, My project is in MS Access. In that I have one Form(Search) which I am using for Search data in my Table(PRODUCTION). Now in this form I have one Combobox(PalletNo) and one button(Search)...

Microsoft Access / VBA

PHP and MySQL multiple table search query help

by: craigcairley | last post by:

Hi guys and gals, I am trying to do a search of a table in my DB but one of the fields is CompanyID which links to another table that has all the company information so I don't have to write the...

PHP

accessing data from table error = no value given for one or more required parameters

by: rahul more | last post by:

My aim is - Access data from table and show it into a form's textboxes , comboxes for edition of data. I am using listview. I am selecting particular row of listview by clicking on that row and...

Visual Basic 4 / 5 / 6

Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"

by: taylorcarr | last post by:

A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...

General

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Basic Javascript concepts

by: aa123db | last post by:

Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...

Javascript

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

How to search data from multiple table in mysql using php

Similar topics