Showing posts with label mysql. Show all posts
Showing posts with label mysql. Show all posts

Monday, October 17, 2011

MySQL resultset as an associative array

To save time and resources, when a php application is going to require the same resultset often, the resultset can be saved in a session as an associative array. Then when required in the script the saved session data can be referenced instead of querying the database again.

To return a resultset as an array named $ac_arr requires the following mysql...

$result=mysql_query("SELECT name,startdate,enddate FROM accounts") or die(mysql_error());

while(($ac_arr[] = mysql_fetch_assoc($result)) || array_pop($ac_arr));

The array_pop() function simply removes the extra element that would otherwise be appended to the array.

This can then be stored in a session variable and later referenced without re-querying the database...


$_SESSION['ac_array']=$ac_arr;

To list the keys and values of elements in the array

<?php
$acs=$_SESSION['ac_array'];
foreach($acs as $keys=>$values)
{
foreach ($values as $key => $value) {
echo $keys." : ".$key.":".$value."<br />";
}
}
?>

The above will display a list like this
0 : name : AC1
0 : startdate : 2011-08-16
0 : enddate : 2011-12-16
1 : name : AC2
1 : startdate : 2014-07-25
1 : enddate : 2024-07-25
2 : name : AC3
2 : startdate : 2012-05-10
2 : enddate : 2022-05-10

so you can reference any element in the array by calling
$acs[0][name] (will return AC1)
$acs[2][startdate] (will return 2012-05-10)

Tuesday, July 19, 2011

mysql compare two large tables - find records only in 1 table - for sitemap last modified date

I needed to compare two large tables each having over 2 million records and find the records that are in table 1 but not in table 2.

I only wanted the records from table 1 and not all records that were different from both tables, so on each select query I added a temporary column that stated which table the result was from, so that after the insert select, those with table2 in the temp column could be deleted, leaving just the table 1 records...which are new records that have been added to a database since the last update.

The UNION results are inserted into a new table, so that after being inserted, all records from table2 can be deleted...


INSERT INTO products_new (name,merchantid,tab)
SELECT MIN( name ) AS name, merchantid, tab
FROM (
SELECT name, merchantid, 'table1' AS tab
FROM products_import AS alias1
UNION ALL SELECT name, merchantid, 'table2' AS tab
FROM products_bak AS alias2
)AS alias_table
GROUP BY name
HAVING COUNT( name ) =1
ORDER BY name

So after populating the new products table, the records that were only in table 2 can be deleted, leaving a table with the unique values from table 1.


DELETE FROM products_new WHERE tab='table2'

I have used this in order to be able to make my sitemaps last modified date more accurate, as currently at each product update, all dynamically created product pages have a recent last modified date, even if they were previously present in the DB. Using this method, the last modified date of new products can be accurately shown as new pages, by accessing this new table.

Saturday, July 09, 2011

MYSQL - Update one columns data from another table

To copy one columns data to another existing tables column in mysql, it is possible with an update query like this :

UPDATE table1,table2
SET table1.coltocopy=table2.coltocopy

WHERE table1.coltocompare=table2.coltocompare;

Monday, June 27, 2011

PHP - Assign variable names to array elements from mysql query

It is useful to be able to take the results of a MYSQL query and assign variable names to the results, so that they can be used later on in a script.

This can be done simply as follows...


$result= mysql_query("SELECT id FROM table WHERE num='567' LIMIT 3") or die(mysql_error());

while ($row = mysql_fetch_array($result)) {

$ids[] = $row['id'];

}
$id1 = $ids[0];
$id2 = $ids[1]
$id3 = $ids[2];


So you now have 3 variables fetched from the mysql array that can be used outside of a loop.

This specific example limits the mysql result to 3. However you could do this for any (change the 3 to how many results you want) and unknown numbers of results doing a COUNT of returned results and then iterating based on that number.