PHP,SQL multiple Tables

Question

im in need of help. I created a search for my website. Now i have created a second table in the database with older data. I want to retreive that data on the same search but I can't find the right way to do it. I have read some tutorials and tried stuff but I just cant figure it out how to do it properly.

    $raw_results = mysql_query("SELECT BSTKATNR, BSTLIEFBST, BSTARTBES1, BSTANF, BSTSTLIO, BSTLIEFMIN, BSTMIND, BSTARTMASS, BSTKUMST, BSTKUMVK, BSTKUMER  FROM elebest
    WHERE 
    ( `BSTLIEFBST` LIKE '%".$query."%' )
    OR 
    ( `BSTKATNR` LIKE '%".$query."%' )
    OR 
    ( `BSTLIEFTXT` LIKE '%".$query."%' )
    OR 
    ( `BSTARTBES1` LIKE '%".$query."%') ") or die(mysql_error());

This is the code that works and searches everything I need. Is it somehow possible to select 3 columns from the table "olddata" ? I hope someone can help me. The table "olddata" only has 3 columns so I don't think that JOIN would work...

Thank you.

1. Use prepared statements php.net/manual/en/mysqli.quickstart.prepared-statements.php 2. Look into a subqueries. It is impossible to answer your question without more information. — kyle
– kyle, Commented Jan 9, 2017 at 16:57
And could you post the table structures in which you're searching and how these tables relate to each other? — Fyntasia
– Fyntasia, Commented Jan 9, 2017 at 16:58
One table has 40 columns and the other one with old data has 3 columns. The first table has the data from the current year and the other table has old data from last year. — Exelion
– Exelion, Commented Jan 9, 2017 at 17:03

Fyntasia · Accepted Answer · 2017-01-09 19:34:25Z

3

It sounds to me you should run two seperate searches, one for new data, one for old data. If you have seperate tables with seperate data from seperate timestamps. You can't use a JOIN if there are no overlapping fields.

Here's a PDO snippet you can use for safer queries:

$dbHost = 'databaseHost';
$dbName = 'databaseName';
$dbUser = 'databaseUsername';
$dbPass = 'databasePassword';

/**
 * Connect to your database.
 */
try
{
    $dsn = 'mysql:dbname=%s;host=%s';
    $conn = new PDO(vsprintf($dsn, [$dbName, $dbHost]), $dbUser, $dbPass);
} catch (PDOException $e)
{
    /**
     * Catch any exceptions in case your connection should fail.
     */
    echo 'Failed to connect: '.$e->getMessage();
    die();
}
/**
 * Build your queries.
 */
$firstQuery = 'SELECT 
    t1.`BSTKATNR`,
    t1.`BSTLIEFBST`,
    t1.`BSTARTBES1`,
    t1.`BSTANF`,
    t1.`BSTSTLIO`,
    t1.`BSTLIEFMIN`,
    t1.`BSTMIND`,
    t1.`BSTARTMASS`,
    t1.`BSTKUMST`,
    t1.`BSTKUMVK`,
    t1.`BSTKUMER`
    FROM `table1` AS t1 WHERE 
    ( t1.`BSTLIEFBST` LIKE :BSTLIEFBST ) OR
    ( t1.`BSTKATNR` LIKE :BSTKATNR ) OR
    ( t1.`BSTLIEFTXT` LIKE :BSTLIEFTXT ) OR
    ( t1.`BSTARTBES1` LIKE :BSTARTBES1);'
;
$secondQuery = 'SELECT 
    t2.`BSTKUMSTVJ`,
    t2.`BSTKUMVKVJ`,
    t2.`BSTKUMERVJ`
    FROM `vorjahr` AS t2 WHERE 
    ( t2.`BSTKUMSTVJ` LIKE :BSTKUMSTVJ) OR
    ( t2.`BSTKUMVKVJ` LIKE :BSTKUMVKVJ) OR
    ( t2.`BSTKUMERVJ` LIKE :BSTKUMERVJ);'
;
/**
 * Prepare your statements
 */
$stmtOne = $conn->prepare($firstQuery);
$stmtTwo = $conn->prepare($secondQuery);
/**
 * Bind your query to all values.
 */
$stmtOne->bindValue(':BSTLIEFBST', '%'.$query.'%', PDO::PARAM_STR);
$stmtOne->bindValue(':BSTKATNR', '%'.$query.'%', PDO::PARAM_STR);
$stmtOne->bindValue(':BSTLIEFTXT', '%'.$query.'%', PDO::PARAM_STR);
$stmtOne->bindValue(':BSTARTBES1', '%'.$query.'%', PDO::PARAM_STR);

$stmtTwo->bindValue(':BSTKUMSTVJ', '%'.$query.'%', PDO::PARAM_STR);
$stmtTwo->bindValue(':BSTKUMVKVJ', '%'.$query.'%', PDO::PARAM_STR);
$stmtTwo->bindValue(':BSTKUMERVJ', '%'.$query.'%', PDO::PARAM_STR);
/**
 * Execute the statement
 */
$stmtOne->execute();
$stmtTwo->execute();
/**
 * Return your resultset
 */
$resultset1 = $stmtOne->fetchAll();
$resultset2 = $stmtTwo->fetchAll();

Just create two of these and combine your results!

edited Jan 9, 2017 at 19:34

answered Jan 9, 2017 at 17:10

Fyntasia

1,1436 silver badges19 bronze badges

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

6 Comments

Exelion Over a year ago

wow.. you overwhelmed me. thank you so much already. I tested it out and it works! Now, is there a certain way I can/have to 'echo' results in PDO ?

Exelion Over a year ago

okay.. I have figured out how I can echo my results. Was rather easy actually... Back to the problem - How can i reach 2 tables? You said create 2 - as in create 2 files with different tables? But how would i go and combine them later? Im very new to all of this stuff.. Hope you can help :)

Fyntasia Over a year ago

You can use a simple array_merge($resultset1, $resultset2). If your issue is resolved and your question answered, feel free to accept my answer <3

Exelion Over a year ago

I most certainly am going to accept your answer after this last thing. I just don't get the multiple query stuff yet.. This is how I changed your Code now ( don't hit me (excuse me, it's in german)) pastebin.com/P11wEsAx Now I need the columns BSTKUMSTVJ, BSTKUMVKVJ and BSTKUMERVJ from the table "vorjahr" Tried multiple things but as I said, I don't quite get it yet.. sorry

Fyntasia Over a year ago

My latest edit should get you 2 resultset for both databases.

|

marc_s · Accepted Answer · 2017-01-16 20:19:09Z

-1

You can use joins for retrieving multiple data from different tables:

select 
    table1.id, table1.name, table2.id, table2.name 
from 
    table1 
left join 
    table2 on (table1.id = table2.id) 
where 
    (table1.`BSTLIEFBST` LIKE '%".$query."%')
    or (table1.`BSTKATNR` LIKE '%".$query."%')
    or (table2.`BSTLIEFTXT` LIKE '%".$query."%')
    or (tabl2.`BSTARTBES1` LIKE '%".$query."%')

I hope it will help you. If you have still confusion the please commits your tables with field and specify which columns you need I will create query for you

edited Jan 16, 2017 at 20:19

marc_s

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

answered Jan 9, 2017 at 17:09

Dani

9257 silver badges14 bronze badges

2 Comments

Fyntasia Over a year ago

There are no table ID's given by the OP and if the second table contains 'old' data (archieved data). There is no way you can just join them on their ID.

Dani Over a year ago

can you please share your database table along with data

Collectives™ on Stack Overflow

PHP,SQL multiple Tables

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related