How to retrieve data from multiple tables using a PHP form?

Question

I want to retrieve data from multiple tables using dot operator/join where arguments are passed from an HTML/PHP form.

HTML CODE

<input name="rollno" type="text" placeholder="Roll Number" required>
<input name="submit_view_details" type="submit" value="Proceed">

PHP CODE

if(isset($_POST['submit_view_details']))
{
    $rollno = (int) $_POST['rollno'];
    $query = "select * from table1, table2 where table1.{$rollno}=table2.{$rollno}";
    $result=mysqli_query($connection,$query);
}

In the browser if enter the input 1 and echo this query then it looks like follows:
select * from table1, table2 where table1.1=table2.1

and no row is fetched despite of having data in the table(s).

it only works if the query looks like follows:
select * from table1,table2 where table1.rollno=table2.rollno

However, in that case it fetches all the rows but I need only the row of the rollno that user entered in the above mentioned form.

I am just not able to work this out. Help would be much appreciated. Thanks!

try this $query = "select * from table1, table2 where table1.rollno=table2.rollno AND table1.rollno = $rollno"; — Ganesh Patil
– Ganesh Patil, Commented Apr 29, 2015 at 10:55

Coloco · Accepted Answer · 2015-04-29 10:54:37Z

3

Use the AND keyword to specify the rollno.

SELECT * FROM table1, table2 WHERE table1.rollno = table2.rollno 
AND table1.rollno = {$rollno};

You could probably use the keyword JOIN instead like this :

SELECT * FROM table1 NATURAL JOIN table2 
WHERE rollno = {$rollno};

answered Apr 29, 2015 at 10:54

Coloco

3534 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Meenesh Jain · Accepted Answer · 2015-04-29 11:00:11Z

1

You need joins

take a reference of joins from here,

i am sure it will help

http://www.tutorialspoint.com/mysql/mysql-using-joins.htm

edited Apr 29, 2015 at 11:00

answered Apr 29, 2015 at 10:54

Meenesh Jain

2,5262 gold badges21 silver badges29 bronze badges

2 Comments

Daan Over a year ago

There is no such thing as mysqli or mysql JOINS. JOINS are just standard SQL.

Meenesh Jain Over a year ago

if my answer was helpful can you mark it as useful, it will be helpful to others too

marc_s · Accepted Answer · 2015-08-26 20:56:30Z

0

You should use join like this

$query = "SELECT tbl1.*, tbl2.* 
          FROM tbl1 
          INNER JOIN tbl2 ON tbl1.id = tbl2.id 
          WHERE tbl1.column = value ";

edited Aug 26, 2015 at 20:56

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Apr 29, 2015 at 10:55

Manoj Dhiman

5,1616 gold badges32 silver badges71 bronze badges

1 Comment

Mekey Salaria Over a year ago

How can i add LIMIT to it?

Matt S · Accepted Answer · 2017-07-21 15:44:26Z

0

foreach ($pieces_2 AS $value) {
         $pieces_3[] ="(CONCAT_WS('|',$presql2) like '%$value%')";      //concat all columns from one table
 }
 $serch_jyouken = implode(" and ",$pieces_3);       // for multiple keywords                    
 $result1 = mysqli_query($connection, "select p.p_no from pfr_data p where (" .$serch_jyouken .")");
 $res1 = array();
 while($r1 = mysqli_fetch_array($result1){
         $res1[] = $r1['p_no'] ;        //fetch primary key from table and store it into array 
 }
 foreach ($pieces_2 AS $value) {            
         $pieces_4[] ="(CONCAT_WS('|',$presql3) like '%$value%')";      // same as above
 }
 $serch_jyouken1 = implode(" and ",$pieces_4);
 $result2 = mysqli_query($connection, "select p2.p_no from pfr_mod_inform p2 where (" .$serch_jyouken1 .")" );
$res2 = array();
 while($r2 = mysqli_fetch_array($result2)){                                                                               
         $res2[] = $r2['p_no'];
 }
 $res_mrg = array_merge($res1 , $res2);     //merge array
 $result = implode("','",$res_mrg );        // array to sring
 $sql5 = $presql ." from pfr_data p where p.p_no in ('$result') order by p.section_p,p.status,p.no";

edited Jul 21, 2017 at 15:44

Matt S

15.5k6 gold badges60 silver badges79 bronze badges

answered Jul 21, 2017 at 13:00

Tushar Lathiya

1,10810 silver badges28 bronze badges

1 Comment

Tushar Lathiya Over a year ago

this is long method but i sure it will help & i will give result within second

Collectives™ on Stack Overflow

How to retrieve data from multiple tables using a PHP form?

4 Answers 4

Comments

2 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related