Archive for the ‘MySQL’ Category

MySQl sub query example 2

Tuesday, August 17th, 2010

SELECT
q.questionId,
q.question,
q.questionTopic,
q.questionDate,
q.courseId,
q.customerId,
v.voted_this
FROM
” . $course_database_questions_table_name . ” q
LEFT JOIN
(SELECT questionId voted_this FROM votes WHERE customerId = ‘” . $customer_id . “‘) v
ON
q.questionId = v.voted_this
WHERE
q.courseId = ” . $courseId . ”
ORDER BY q.questionDate DESC

MySQL sub query example – nice

Monday, August 16th, 2010

$cp_output = “”;
if(!$get_cp_data = mysql_query(”
SELECT
q.*,
c.*,
vote_counts.Tally
FROM
questions q
LEFT JOIN
(SELECT
questionId,
count(1) as Tally
FROM
votes
GROUP BY
questionId
) vote_counts
ON
q.questionId = vote_counts.questionId
LEFT JOIN
customers c
ON
c.customerId = q.customerId
WHERE
q.courseId = 1
ORDER BY
vote_counts.Tally desc

“)){ echo mysql_error(); }

while($get_cp_data_array = mysql_fetch_array($get_cp_data)){
$q_id = $get_cp_data_array['questionId'];

$q_fname = $get_cp_data_array['customerFName'];
$q_lname = $get_cp_data_array['customerLName'];
$q_email = $get_cp_data_array['customerEmail'];

$q_topic = $get_cp_data_array['questionTopic'];
$q_question = $get_cp_data_array['question'];
$q_date = $get_cp_data_array['questionDate'];
//$q_count = $get_cp_data_array['votecount'];
$q_customer = $get_cp_data_array['customerId'];

if($q_topic == “”){ $q_topic = “–”; }
if($q_fname != “” || $q_lname != “”){
$from = ‘<strong>’ . $q_fname . ‘ ‘ . $q_lname . ‘</strong> – ‘;
}

# Parse the question text.
$old = array(“\n”);
$new   = array(“<br/>”);
$q_question = str_replace($old, $new, $q_question);

# Output the question.
$cp_output = $cp_output . ‘
<div>’ . $q_id . ‘</div>
<div>
<div><strong>’ . $q_topic . ‘</strong></div>
<div>’ . $q_question . ‘</div>
<div><strong>Submitted</strong> ‘ . $q_date . ‘ by ‘ . $from . ‘<a href=”mailto:’ . $q_email . ‘”>’ . $q_email . ‘</a></div>
</div>
<div></div>
‘;
}

MySQL refresher

Tuesday, July 27th, 2010

datatypes:

http://w3schools.com/sql/sql_datatypes.asp

Order results by 2 columns:

SELECT column_name(s)
FROM table_name
ORDER BY column1, column2

Simple delete:

<?php
$con = mysql_connect(“localhost”,”peter”,”abc123″);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}
mysql_select_db(“my_db”, $con);
mysql_query(“DELETE FROM Persons WHERE LastName=’Griffin’”);
mysql_close($con);
?>

using a PHP variable in a MySQL IN batch query example

Friday, July 16th, 2010

Note the ‘s

$product_list = “’1869′, ’1929′, ’549′, ’189′, ’419′, ’176′, ’192′, ’310′, ’291′, ’514′, ’1643′, ’1033′;

$db_name = “dbName”;
//conditional platform logic to support multiple connection scenarios

switch($_SERVER['HTTP_HOST'])
{
case ‘localhost’:
$db_host = “localhost”;
$db_username = “userName”;
$db_password = “Password”;
$dblink = mysql_connect ($db_host, $db_username, $db_password);
mysql_select_db($db_name, $dblink);
break;
case ‘www-staging.site.com’: break;
case ‘www.site.com’:
include(‘/var/www/includes/connection_script.php’);
break;
}

$result = mysql_query(”
SELECT
product_id,
product_name
FROM
tableNameHere
WHERE
product_id IN
(“.$product_list.“)
“);
if (!$result) { echo “Could not successfully run query ($sql) from DB: . mysql_error(); exit; }


//let’s break out the resource so that it can be singularly requested when needed

while($one = mysql_fetch_assoc($result))
{
$prod_id = $one['product_id'];
$product_name[$prod_id] = $one['product_name'];
}
@
mysql_close($dblink);

MySQL phpMyAdmin text string find replace Query

Sunday, April 25th, 2010

update TABLE_NAME set FIELD_NAME =
replace(FIELD_NAME,’find this string’,'replace it with this one’);

example (for videodb to turn all “wanted” titles into “DVD” titles):

update videodb_videodata set mediatype =
replace(mediatype,’50’,’1’);

PHP MySQL date & time reference

Wednesday, April 21st, 2010

http://www.eltcalendar.com/stuff/datemysqlphp.html

thank you for this great handy reference!

pasted verbatim:

Format PHP MySQL
Time The function is:
date(format)
where “format” consists of the letters given below.
The functions are:
date_format(string, format)
time_format(string, format)
where “format” consists of the letters given below.)
hour, 2-digit, 12-hour (01-12) h %h
hour, 2-digit, 24-hour (00-23) H %H
hour, numeric, 12-hour (1-12) g %l
hour, numeric, 24-hour (0-23) G %k
minute, 2-digit (00-59) i %i
seconds, 2-digit (00-59) s %S
time, 24-hour (hh:mm:ss) none, use date(“H:i:s”) %T
time, 12-hour (hh:mm:ss AM/PM) none, use date(“g:i:s A”) %r
AM / PM (uppercase letters) A %p
am / pm (lowercase letters) a none
Days
day name, full (Sunday) l (a lowercase L) %W
day name, abbreviation (Sun) D %a
day as number of week (0-6) none, use a getdate() array:
First do this:
$dateInfo = getdate();
Your day as number of the week is $dateInfo[wday];
%w
day number of the year z
(Note: 0-365; ex: January 1st is 0)
%j
(Note: 001-366; ex: January 1st is 001)
Dates
month name in full (January) F %M
month name, abbreviated (Jan) M %b
month, 2-digit (01-12) m %m
month, no leading 0 (1, 2, 3 – 12) n %c
day of month, 2-digit (01, 02, …) d %d
day of month, no leading 0 (1, 2) j %e
day of month with ordinal suffix (1st, 2nd, 3rd, 4th…) none. Use jS %D
ordinal suffix (st, nd, rd, th) S none; use %D (above)
year, 4 digit (2001) Y %Y
year, 2 digit (00-99) y %y
Examples: To write: Use this: Use this:
Sunday, June 03, 2001 date(‘l, F d, Y’) date_format(date, ‘%W, %M %d, %Y’)
Sun., Jun 3rd, 2001. 11:30 AM date(‘D., M jS, Y.g:i A’) date_format(datetime, ‘%a., %b %D, %Y. %l: %i %p’)
Or if there are two columns, one date and one time, you’d use:
date_format(date, ‘%a., %b %D, %Y.’)
time_format(time, ‘%l: %i %p’)
See the note below)
2001-06-03 date(‘Y-m-d’) (see important note below; this is default for date-type columns so you don’t have to use any special function or formatting at all.)

Important Note:

The choice in MySQL between date_format() and time_format() depends on the type of column you have stored your date in.

MySQL column type: Data format in the column (all parts are required) Example: Use:
date YYYY-MM-DD 2001-06-03 date_format() to get date values
time (see “NB” below) hh:mm:ss 13:30:00 time_format() to get time values
datetime YYY-MM-DD hh:mm:ss 2001-06-03 13:30:00 date_format() to get date and/or time values

NB: the time type for MySQL columns actually represents an amount of time elapsed; thus, it can be negative or positive. However, it can also be used to represent the time of day, where it represents the amount of time elapsed since 1 second past midnight. If you forget to add :00 for seconds (for example, using 13:30 for 1:30 p.m.), your time value will be interpreted as the number of minutes and seconds after midnight, or 12:30 a.m. plus 30 seconds.

MySQL phpMyAdmin Query change table names syntax

Tuesday, April 13th, 2010

RENAME TABLE

database_name.table_name TO database_name.new_table_name,

database_name.table2_name TO database_name.new_table2_name;

NOTE the comma and the semicolon!

phpMyAdmin Server config.inc.php remote database connection

Monday, March 15th, 2010

C:\xampp\phpMyAdmin\config.inc.php

add this (or similar) at the bottom:

$i++;

$cfg['Servers'][$i]['auth_type'] = ‘config’;
$cfg['Servers'][$i]['user'] = ‘user’;
$cfg['Servers'][$i]['password'] = ‘password’;
$cfg['Servers'][$i]['host'] = ‘your_host’;
$cfg['Servers'][$i]['verbose'] = ‘Description to appear in phpMyAdmin’;

//end

params/reference: http://wiki.phpmyadmin.net/pma/Config

And then control remote MySQL databases in your localhost/phpmyadmin

under Actions: Server: dropdown

*note: When connecting to a Godaddy DB, you must enable accessible remotely when creating the database!

Php db connection close()

Monday, March 8th, 2010

@mysql_close($dblink);

or the network persistence traffic cop might come calling…YOU

MySQL comments

Monday, March 8th, 2010

start of line left only (must have space after dashes):
– comment

OR
/* */

example:

if(!$result = mysql_query(”
SELECT
products_id,
FROM
runwayProductInfo,
WHERE
products_id IN (’545′,’100′)
/* hello */
– comment
ORDER BY FIELD(runwayProductInfo.products_id, ’100′,’545′)
“)){ echo mysql_error(); exit; }

Php MySQL query shortcut syntax

Monday, March 8th, 2010

$query = “SELECT
o.FirstName, o.LastName, o.Addr1, o.Addr2, o.City,
s.Name AS State, s.Abbrv as StateAbbrv, o.ZipCode,
o.Phone1, o.EmailAddr, o.CCLastFour, o.CCExp, o.PromoCode
FROM orders o
INNER JOIN states s ON s.StateID = o.StateID
WHERE o.OrderID = ” . $this->_orderID;