How to check with PHP does a SQL database already have
Question by Dan Horvat
I’ve tried to find the answer to this question but none of the answers fit.
I have two databases, one has 15.000.000 entries and I want to extract the necessary data and store it in a much smaller database with around 33.000 entries. Both databases are open at the same time. Or at least they should be.
While having the big database open and extracting the entries from it, is it possible to check whether the value already exists in a certain table in the smaller database? I just need some generic way which checks that.
In my code I’m first opening both databases (big one is oddsnavi_push, small one is oddsnavi_baby):
$database = "oddsnavi_push";
$db_handle = mysql_connect($server, $user_name, $password);
$db_found = mysql_select_db($database, $db_handle);
$database_baby = "oddsnavi_baby";
$db_handle_baby = mysql_connect($server, $user_name, $password);
$db_found_baby = mysql_select_db($database_baby, $db_handle_baby);
And then I’m starting to read and calculate data from oddsnavi_push:
$SQL_SELECT_ALL = "...giant query...";
$result_select_all = mysql_query( $SQL_SELECT_ALL );
while($db_field_all = mysql_fetch_assoc( $result_select_all ) ) {
$SQL_INSERT="INSERT INTO oddsnavi_baby.calc (id, one, two) VALUES ('$id', '$one', '$two')";
It works up until that point. It takes the data which was read (id, one, two) and inserts them in proper columns in oddsnavi_baby table named calc. It does that properly when the oddsnavi_baby is completely empty.
However, I need it to only update the database IF an entry (based on whether a certain ‘id’ exists or not) doesn’t exist.
EDIT: I will rephrase my question. From the query results (big database) I’m getting strings, for every row. For example $string. How do I open the second database and check if oddsnavi_baby.calc table has the $string value in column Events?
Answer by barsju
Skip the check and try with just INSERT IGNORE assuming the Id is a unique key.
Answer by Starx
Why do you really need multiple database? Amount does not matter tables are just fine, no need to split.
//Multiple links to different databases
$dblink1 = mysqli_connect($localhost, $user, $pass, $db1);
$dblink2 = mysqli_connect($localhost, $user, $pass, $db2);
$id = '1'; // Id to check
$query = "SELECT COUNT(*) FROM `table` WHERE id = '1' LIMIT 1";
$result = mysqli_query($dblink1, $query); //query db 1
if(mysql_num_rows($result)) {
$query = "INSERT INTO `table` VALUES(.....)";
$result = mysqli_query($dblink1, $query); //query db 2
}
