CONCAT
Adding record data to a string
APENDING data before and after a field.
UPDATE `iwspackage` SET iwspackageimg=CONCAT(‘package’,packageid,’.jpg’);
UPDATE ads SET img1=CONCAT(adid,’a.jpg’), img2=CONCAT(adid,’b.jpg’) , img3=CONCAT(adid,’c.jpg’) , img4=CONCAT(adid,’d.jpg’), pdf=CONCAT(adid,’.pdf’) WHERE adid>7;
DATE
ADDDATE('1998-01-02', INTERVAL 31 DAY);
SELECT invoices.customerid, `last_name` , `first_name`, COUNT(`last_name`) AS dog,
count(if(`date`< ADDDATE(NOW(), INTERVAL -365 DAY), 1, null)) AS year1, count(if(`date`>= ADDDATE(NOW(), INTERVAL -365 DAY), 1, null)) AS year0, count(if(`date`< ADDDATE(NOW(), INTERVAL -730 DAY), 1, null)) AS year2
FROM `invoices`
INNER JOIN `customer` ON customer.customerid = invoices.customerid
GROUP BY invoices.customerid, `last_name` , `first_name`
ORDER BY year0,year1
Group By Using Left Join
Link the inventory of a product used to the amount purchased for easy selection of available product. Example used via crop map inventory selection form.
$orderby=" GROUP BY product ";
$STID=$productid;
$query="SELECT products.productid,products.product,products.number_bags,SUM(inventory.amount)AS usedcount FROM products LEFT JOIN inventory ON products.productid=inventory.productid $where_product $orderby ";
$r->multipleRecs(true); $r->dbsql($query);$result=$r->data;$num=$r->num;
$dis1.= "<tr><td align=right>x:<td><select id='comproductid' name='productid' size='1'><option value=z>—————-\n";
For ($i=0; $i< $num; $i++){
$row=mysql_fetch_array($result);
$productid=replacepunctuation($row['productid']);
$product=replacepunctuation($row['product']);
if ($productid == $STID){
$dis1 .= "<option selected value='$productid'>$product ".$row["number_bags"]."=>{".$row["usedcount"]."}\n";
}
else{
$dis1 .= "<option value='$productid'>$product ".$row["number_bags"]."=>{".$row["usedcount"]."}\n";
}
}
if($grouping==true)$dis1 .= "</optgroup>";
$dis1 .= "</select><br>";
// end combo
Group By Inner Join 3 level
SELECT YEAR(date), category,item, SUM(invoice_cart.quantity), SUM(invoice_cart.price*invoice_cart.quantity) FROM invoices INNER JOIN (invoice_cart INNER JOIN (items INNER JOIN item_category ON items.categoryid=item_category.categoryid) ON items.itemid=invoice_cart.itemid) ON invoices.invoiceid=invoice_cart.invoiceid WHERE service=1 OR service=2 GROUP BY YEAR(date),category,item”;
Insert using Select
INSERT INTO exportregistration( `registrationid` , `groupid` , `event` , `dateof` , `lastname` , `firstname` , `address` , `city` , `state` , `zip` , `home_phone` , `email` , `special_email` , `relationid` , `arrived` )
SELECT `registrationid` , `groupid` , `event` , `date` , `lastname` , `firstname` , `address` , `city` , `state` , `zip` , `home_phone` , `email` , `special_email` , `relationid` , `arrived`
FROM EVENTS INNER JOIN event_registration ON events.eventid = event_registration.eventid ORDER BY date, lastname, firstname
Insert specifying the record id
If I have entered 10 records, and deleted center records – 4th, 5th. I want to insert next record as 4th not 11th.
Solution:
Run the following query:
SET insert_id = 4;
INSERT INTO tablename VALUES (’blah’, ‘…’);
This will add the next record into record 4th.
The SET insert_id = #(where # is the next auto increment value you want to use) will reset the next auto increament value, the next query(INSERT) you run will use your choice of value.
Note: only effective for the immediate next query, one time.
Nulls
Select * from tableparent left join tablechild on tableparent.musgraveid=tablechild.musgraveid Where tablechild.musgraveid IS NULL LIMIT 0, 30
Using HAVING
count the check writers in their invoices during the last year and accept only those who write more than two checks.
SELECT `first_name`,`last_name`, COUNT(`paidby`) AS chk FROM `customer` INNER JOIN invoices on customer.customerid=invoices.customerid WHERE date>ADDDATE(NOW(), INTERVAL -365 DAY) GROUP BY `last_name` HAVING chk>1