html search form linked with MySQL db

Question

I am new to PHP and MySQL and struggle to extract data from db based on html search form containing several fields. 1st field (*required) = select with options (select name = area_name), 2nd = input type: school name, 3rd = date, 4-5th = time_from, time_to. Question: how may I extract lecturers' names based on above fields?

Below is the PHP code (ignore db connect, it's working):

    if (!empty($_REQUEST['term'])) {
    $term = mysql_real_escape_string($_REQUEST['term']);     

    (line 8)
    $sql = mysqli_query("SELECT lecturer_name, city, phone, e-mail * FROM 
    area_name where area_name LIKE '$search'") UNION ("SELECT * FROM school 
    where school_name LIKE '$search'") UNION ("SELECT * FROM schedule where 
    date LIKE '$search'") UNION ("SELECT * FROM schedule where time_from 
    LIKE '$search'") UNION ("SELECT * FROM schedule where time_to LIKE 
   '$search'");
    $r_query = mysqli_query($sql); 

    echo "<table border='1' cellpadding='5'>";
    echo "<tr> <th>Lecturer Name</th> <th>City</th> <th>Phone</th> 
    <th>Email</th> <th></th> <th></th></tr>";

    // loop through results of database query, displaying them in the table
    while ($row = mysql_fetch_array($r_query)){

            // echo out the contents of each row into a table
            echo "<tr>";
            echo '<td>' . $row['lecturer_name'] . '</td>';
            echo '<td>' . $row['city'] . '</td>';
            echo '<td>' . $row['phone'] . '</td>';
            echo '<td>' . $row['email'] . '</td>';
            echo "</tr>"; 
    } 

    // close table>
    echo "</table>"; 

    }

    $conn->close();

In the result, have the following error:

Parse error: syntax error, unexpected 'UNION' (T_STRING) in D:\XAMPP\htdocs\trv\search_lecturer.php on line 8

I can't get how to connect html field names with mysql. Will really appreciate any help!

Why do you have * in `SELECT lecturer_name, city, phone, e-mail *`? — Edward, Mar 11 '18 at 17:55
You should separate your tasks to learn how these things are done. You are trying to learn PHP and MySQL at the same time. First learn MySQL on the command line, learn how to build queries. When you have that, move on to PHP. — john elemans, Mar 11 '18 at 18:02
Your query is not safe from injection attacks. Please research mysqli's prepared statements with placeholders for security reasons. There are `LIKE`-specific questions on StackOverflow that display how to do this. — mickmackusa, Mar 12 '18 at 05:24
You must not mix `mysql_` with `mysqli_`. `UNION` is not a function that concatenates `mysqli_query()`. It needs to be inside of the query string -- in other words, inside of the double quotes. — mickmackusa, Mar 12 '18 at 05:30

score 0 · Answer 1 · answered Mar 11 '18 at 18:05

0

UNION is SQL operator, not PHP. You have errors in the PHP syntax.

$sql = mysqli_query("(SELECT .....) UNION (SELECT .....) UNION .....");

P.S. * means all fields, if you specify the fields, * isn't required.

answered Mar 11 '18 at 18:05

toor

101
5

Hello Toor, thank you! What if I need to select the same data from different mysql tables? ex. $sql = mysqli_query("(SELECT lecturer_name, city, phone, e_mail FROM area WHERE area_name LIKE '$search') UNION (SELECT lecturer_name, city, phone, e_mail FROM school WHERE school_name LIKE '$search') UNION ....."); – Yelena TsK Mar 11 '18 at 18:10
I have searched similar topics, but fail to find my case when I need info which is not shown in html search fields, it's just related to it. (need lecturer data from area, school and schedule tables) – Yelena TsK Mar 11 '18 at 18:15
If you put a php variable, I suggest separate it. mysqli_query("(SELECT lecturer_name, city, phone, e_mail FROM area WHERE area_name LIKE '".$search."') UNION ... everything else is correct. – toor Mar 11 '18 at 18:30
1

No. Use prepared statements with placeholders. – mickmackusa Mar 12 '18 at 05:33

html search form linked with MySQL db

1 Answers1