MySQL update set REPLACE practice string search and replace

update table1 set column1 = REPLACE(column1,"string_to_find","string_to_find_altered")



Posted on 1 Dec 2009, 17:56 - Category: MySQL
Comments - Edit - Delete


Php simple MySQL check for value SELECT, if null INSERT Php

assumption: script already connected to a db

//---------------------------------------------
//IS EMAIL ALREADY CAPTURED?
//---------------------------------------------
$result = mysql_query("SELECT email FROM table WHERE email='$email'");
if(mysql_num_rows($result)==0)
{
//---------------------------------------------
//INSERT
//---------------------------------------------
mysql_query("INSERT INTO table (first_name, last_name, email) VALUES ('$first_name', '$last_name', '$email' ) ")
or die(mysql_error());
}

note: mysql_num_rows returns true or false

keywords: MySQL, Php, PHP, MYSQL, mysql, php, INSERT, insert, SELECT, select, mysql_query, query, mysql_num_rows

Posted on 20 Oct 2009, 13:52 - Category: MySQL
Comments - Edit - Delete


dynamic Php array list IN Mysql query statement also using ORDER BY FIELD function

// This is so that I can use the mysql_query IN clause
// to simplify query hits, resources and so forth, thus
// looping through results array instead of looping
// through it doing a query each time, but the syntax
// to get this working proved a bit daunting...
// Also of note: the ORDER BY FIELD function,
// literally orders the results resource for us!
// so there is no need in this entire query, php echo
// to re-array or order or php juggle the data...

$ids = "'546,','544','513'";

// Notice, it's a STRING!
// so whatever $_GET or $_PUT info is received will
// need some massaging...

//then of course the query itself, something like:

if(!$result = mysql_query("
SELECT
table.colum1,
table.column2,
anothertable.info1,
anothertable.info2
FROM
tabe,
anothertable
WHERE
table.column1 IN ($ids)
AND
table.column1 = anothertable.column1
ORDER BY FIELD(table.column1, $ids)
")){ echo mysql_error(); exit; }

// the IN $ids above (again, it's a STRING) is the
// revelation here

// Then we have everything we want and we can Php // massage it now, something like:

while($one = mysql_fetch_assoc($result))
{
$prod_id = $one['column1'];
$prod_name = $one[$prod_id]['column2'];
$prod_price = $one[$prod_id]['info1'];
$prod_stats = $one[$prod_id]['info2'];
}

Posted on 16 Oct 2009, 15:46 - Category: MySQL
Comments - Edit - Delete


Php MySQL IN clause ONE query instead of foreach multiple queries example

//of course, first connect

$db_name = "database_example_name";
$db_host = "192.111.23...";
$db_username = "user";
$db_password = "pass";

// connection error reporting

if(!$dblink = @mysql_connect ($db_host, $db_username, $db_password)){echo mysql_error();exit;} else{if(!@mysql_select_db($db_name, $dblink)){
echo "Could not connect to the database";
exit;}}


//now, Save resources, instead of:


$product_list_array = array(546, 544, 137);

foreach($product_list_array as $prod_id)
{
$result = mysql_query("SELECT lowest_price, products_image, authors FROM table_name WHERE products_id='$prod_id'");
if (!$result) { echo "Could not successfully run query ($sql) from DB: " . mysql_error(); exit; }

$one = mysql_fetch_assoc($result);

$product_lowest_price[$prod_id] = number_format($one['lowest_price'],2);
$product_image[$prod_id] = $one['products_image'];
$product_author[$prod_id] = $one['authors'];
}
@mysql_close($dblink);

which loops over the array one query per id,
do this
:

$result = mysql_query("SELECT lowest_price, products_image, authors, products_id FROM runwayProductInfo WHERE products_id IN ('546', '544', '137')");
if (!$result) { echo "Could not successfully run query ($sql) from DB: " . mysql_error(); exit; }

while($one = mysql_fetch_assoc($result))
{
$prod_id = $one['products_id'];
$product_lowest_price[$prod_id] = number_format($one['lowest_price'],2);
$product_image[$prod_id] = $one['products_image'];
$product_author[$prod_id] = $one['authors'];
}

// best practice, close your connection

@mysql_close($dblink);

Posted on 12 Oct 2009, 17:49 - Category: MySQL
Comments - Edit - Delete


Pages: [1]
Loading