Showing me only one result?

Question

I have two tables in my database these are:

customer
product

customer table:

customerID
sl_no(pk)
name
email
phone

product table:

product_id(pk)
sl_no(fk)
product
price

When i run this query it shows me only one result:

$sql = (" SELECT * FROM customer INNER JOIN product WHERE customer. sl_no LIKE '%$search%' OR product. sl_no LIKE '%$search%'");

How do you run this query? Most likely you forgot to loop it. — Qirel, Jul 24 '17 at 18:59
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Jul 24 '17 at 19:00
$result_set = mysql_query($sql); if($row = mysql_fetch_array($result_set)) { — Shorif, Jul 24 '17 at 19:10
INNER JOIN needs an `on` clause. `on customer.sl_no = product.sl_no` before `where` and then you only need `where customer.sl_no like '%$search%'` and then you have to iterate though the result set. As it stands, I'm surprised you get any results at all using an inner join w/o an on clause. using an OR makes no sense. if SL_NO in product is a FK it't can't exist unless it's in customer table where it is a pk. so searching for it in product doesn't make sense. — xQbert, Jul 24 '17 at 19:12
And how many results does your query supposed to show? Have you tried running the final query in your favourite MySQL management sw to check the results? — Shadow, Jul 24 '17 at 19:15
@xQbert in MySQL an inner join without an on clause is a valid syntax. You could argue that it should not be, but it is what it is. MySQL creates a Cartesian join in such case. — Shadow, Jul 24 '17 at 19:17
Thus implying it should be an inner join w/ the join between the PK/FK relationship. — xQbert, Jul 24 '17 at 19:33
Please stop using PHP's deprecated mysql api. And start using parametrised queries instead — Strawberry, Jul 24 '17 at 19:36
its working now. but the problem is when i search with sl no it shows this : sl_no: 12 name: Abul Kashem Dulon email: dulon252@gmail.com phone: 654894651 product: Shirt price: 650/- Taka sl_no: 12 name: Abul Kashem Dulon email: dulon252@gmail.com phone: 654894651 product: Pant price: 850/- Taka — Shorif, Jul 25 '17 at 07:37

score 1 · Answer 1 · answered Jul 24 '17 at 19:29

Your sql query will at some point return an error of being ambiguous or column with name, product not found. I have added aliases in the SQL query. If you do not understand. You can read up from here. https://www.w3schools.com/sql/sql_alias.asp

If you

Also try using mysqli because mysql is been deprecated as of PHP 5.5.0

  $servername = "yourhost";
    $username = "username";
    $password = "password";
    $dbname = "myDB";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 

    $sql = (" SELECT customer.name AS name, customer.email AS email, customer.phone AS phone, product.product AS product_name, product.price AS price FROM customer,  product WHERE customer.sl_no = product.sl_no AND customer. sl_no LIKE '%$search%' OR product. sl_no LIKE '%$search%' "); 

    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            echo "<strong>CUSTOMER</strong>";
            echo "Customer Name: " . $row["name"]."<br>";
            echo "Customer email: " . $row["email"]."<br>";
            echo "Customer phone: " . $row["phone"]."<br>";
            echo "----";
            echo "<strong>PRODUCT</strong>";
            echo "Product Name: " . $row["product_name"]."<br>";
            echo "Product Price: " . $row["price"]."<br>";
            echo "-----";
            echo "----";
        }
    } else {
        echo "0 results";
    }
$conn->close();

Don't forget to first test or run query in the database via your UI/phpmyadmin or CLI to understand what result you should expect.

Hope this helps.

score 0 · Answer 2 · answered Jul 24 '17 at 19:17

You may have several issues. As we can't see the Php iteration of the results.... here's some issues with the basic select...

Missing on clause (or change your inner join to a cross join) so your meaning is clear.
The OR in the clause or doesn't make sense if it's an inner join given PK/FK nature of the field.
searching using like %var% seems like bad form. Do you know the exact values? if so use them instead of %var% since this method can't use indexes.
your where customer. sl_no has a space... this won't work.

.

$sql = ("SELECT * 
         FROM customer C 
         INNER JOIN product P 
           ON C.sl_no = P.SL_NO 
         WHERE C.sl_no = '$search'");

If you do really mean like then put the like and %'s back but this seems like bad form. and don't you need . before and after var in php. I'm not a big developer there so maybe I'm thinking of something else.

then I would surmise the problem is in the looping logic in PHP. I think you need to correct more than 1 problem here... some of it in the SQL some if in the PHP. — xQbert, Jul 24 '17 at 19:32

Showing me only one result?

2 Answers2