Re: [PHP-DB] Removing slashes from the database
On 03/06/15 09:37, Aziz Saleh wrote: On Wed, Jun 3, 2015 at 12:25 AM, Ron Piggott ron.pigg...@actsministries.org mailto:ron.pigg...@actsministries.org wrote: On 02/06/15 23:20, Aziz Saleh wrote: On Tue, Jun 2, 2015 at 11:08 PM, Ron Piggott ron.pigg...@actsministries.org mailto:ron.pigg...@actsministries.org wrote: On 02/06/15 22:58, Aziz Saleh wrote: On Tue, Jun 2, 2015 at 10:50 PM, Ron Piggott ron.pigg...@actsministries.org mailto:ron.pigg...@actsministries.org wrote: I am working through the process of removing \'s from the database. I am trying to get this query using a variable starting with $query1 =EOF UPDATE `TABLE_NAME` SET `COLUMN_NAME` = REPLACE(REPLACE(REPLACE(`COLUMN_NAME`,'\\\'','\''),'\\\',''),'','\\'); EOF; But when I go to execute the query I am getting the error: |#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '\''),'\\',''),'\\','\')' at line 1 | Could someone help me know what \ and ' should be part of this query so it will execute correctly --- only removing \'s from the database table text columns? Thank you. Ron When you say remove, as replace all occurrences with an empty string, or replace with a different character? I want \ to become just I want \' to become just ' I also want however \ was escaped to become just \ (I am trying to revert the text back to what it was originally before mysql_escape_string was applied) I hope this helps elaborate. Ron For simplicity sake, do each one in its own query and see which one breaks if any: $query1 =EOF UPDATE `TABLE_NAME` SET `COLUMN_NAME` = REPLACE(`COLUMN_NAME`,'\','') EOF; $query2 =EOF UPDATE `TABLE_NAME` SET `COLUMN_NAME` = REPLACE(`COLUMN_NAME`,\',') EOF; $query3 =EOF UPDATE `TABLE_NAME` SET `COLUMN_NAME` = REPLACE(`COLUMN_NAME`,'','\\') EOF; However, personally, I do not recommend this sort of action. Your data should be escaped in the DB. Your MySQL driver should be handling the escape/un-escape when setting/retrieving the data. A friend pointed out to me today: In the earlier versions of PHP there was a setting called 'magic_quotes_gpc'. When enabled slashes were added by default. This setting has since been depreciated as of PHP 5.3 and was removed completely in PHP 5.4. I am using PHP 5.6. Thank you for the suggestion of running 3 separate commands. Individually these execute successfully. Is it even possible to do a REPLACE in the fashion I have noted? Ron It is possible, but sometimes with the clutter you don't notice a syntax issue. This seems to work fine: $query =EOF UPDATE `TABLE_NAME` SET `COLUMN_NAME` = REPLACE(REPLACE(REPLACE(`COLUMN_NAME`,'','\\'),\','),'\','') EOF; I am still having something weird happening which I don't understand. When I do print_r( $query ); the output is UPDATE `donation_paypal_code` SET `option` = REPLACE(REPLACE(REPLACE(`option`,'\\','\'),\','),'\',''); and I receive the database error ( [0] = 42000 [1] = 1064 [2] = You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '')' at line 1 ) I have confirmed my script has \\ When I search using phpMyAdmin \ turns into and \\ turns into Is this what I should be using in order to get PHP to submit what I want into the database? Thanks, Ron
Re: [PHP-DB] Removing slashes from the database
On 02/06/15 23:20, Aziz Saleh wrote: On Tue, Jun 2, 2015 at 11:08 PM, Ron Piggott ron.pigg...@actsministries.org mailto:ron.pigg...@actsministries.org wrote: On 02/06/15 22:58, Aziz Saleh wrote: On Tue, Jun 2, 2015 at 10:50 PM, Ron Piggott ron.pigg...@actsministries.org mailto:ron.pigg...@actsministries.org wrote: I am working through the process of removing \'s from the database. I am trying to get this query using a variable starting with $query1 =EOF UPDATE `TABLE_NAME` SET `COLUMN_NAME` = REPLACE(REPLACE(REPLACE(`COLUMN_NAME`,'\\\'','\''),'\\\',''),'','\\'); EOF; But when I go to execute the query I am getting the error: |#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '\''),'\\',''),'\\','\')' at line 1 | Could someone help me know what \ and ' should be part of this query so it will execute correctly --- only removing \'s from the database table text columns? Thank you. Ron When you say remove, as replace all occurrences with an empty string, or replace with a different character? I want \ to become just I want \' to become just ' I also want however \ was escaped to become just \ (I am trying to revert the text back to what it was originally before mysql_escape_string was applied) I hope this helps elaborate. Ron For simplicity sake, do each one in its own query and see which one breaks if any: $query1 =EOF UPDATE `TABLE_NAME` SET `COLUMN_NAME` = REPLACE(`COLUMN_NAME`,'\','') EOF; $query2 =EOF UPDATE `TABLE_NAME` SET `COLUMN_NAME` = REPLACE(`COLUMN_NAME`,\',') EOF; $query3 =EOF UPDATE `TABLE_NAME` SET `COLUMN_NAME` = REPLACE(`COLUMN_NAME`,'','\\') EOF; However, personally, I do not recommend this sort of action. Your data should be escaped in the DB. Your MySQL driver should be handling the escape/un-escape when setting/retrieving the data. A friend pointed out to me today: In the earlier versions of PHP there was a setting called 'magic_quotes_gpc'. When enabled slashes were added by default. This setting has since been depreciated as of PHP 5.3 and was removed completely in PHP 5.4. I am using PHP 5.6. Thank you for the suggestion of running 3 separate commands. Individually these execute successfully. Is it even possible to do a REPLACE in the fashion I have noted? Ron
Re: [PHP-DB] Removing slashes from the database
On 02/06/15 22:58, Aziz Saleh wrote: On Tue, Jun 2, 2015 at 10:50 PM, Ron Piggott ron.pigg...@actsministries.org mailto:ron.pigg...@actsministries.org wrote: I am working through the process of removing \'s from the database. I am trying to get this query using a variable starting with $query1 =EOF UPDATE `TABLE_NAME` SET `COLUMN_NAME` = REPLACE(REPLACE(REPLACE(`COLUMN_NAME`,'\\\'','\''),'\\\',''),'','\\'); EOF; But when I go to execute the query I am getting the error: |#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '\''),'\\',''),'\\','\')' at line 1 | Could someone help me know what \ and ' should be part of this query so it will execute correctly --- only removing \'s from the database table text columns? Thank you. Ron When you say remove, as replace all occurrences with an empty string, or replace with a different character? I want \ to become just I want \' to become just ' I also want however \ was escaped to become just \ (I am trying to revert the text back to what it was originally before mysql_escape_string was applied) I hope this helps elaborate. Ron
[PHP-DB] Removing slashes from the database
I am working through the process of removing \'s from the database. I am trying to get this query using a variable starting with $query1 =EOF UPDATE `TABLE_NAME` SET `COLUMN_NAME` = REPLACE(REPLACE(REPLACE(`COLUMN_NAME`,'\\\'','\''),'\\\',''),'','\\'); EOF; But when I go to execute the query I am getting the error: |#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '\''),'\\',''),'\\','\')' at line 1 | Could someone help me know what \ and ' should be part of this query so it will execute correctly --- only removing \'s from the database table text columns? Thank you. Ron
[PHP-DB] MariaDB Database / Table Structure
Hi Everyone. I am wondering if there are database queries that would a: produce the result of all the tables with a database * b: produce the result of all the columns with the specified database table * * I don't want any other details than these names. I am trying to build a WHILE loop that in pseudo code will look roughly like - query to get a list of all the tables within a database - start of a foreach loop (going one table at a time - query to get all the column names within the table - start of a foreach loop to display column names - display column name - end of foreach loop -end of foreach loop Ron
Re: [PHP-DB] MariaDB Database / Table Structure
This is extremely helpful. Thank you very much. SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='.$val.' * where $val is a database On Wednesday November 5 2014 2:14 AM, Roberto Spadim wrote: with mariadb/mysql: 2014-11-05 4:20 GMT-02:00 Ron Piggott ron.pigg...@actsministries.org mailto:ron.pigg...@actsministries.org: Hi Everyone. I am wondering if there are database queries that would SHOW DATABASES; - return all databases a: produce the result of all the tables with a database * SHOW TABLES FROM `database_name`; or USE `database_name`; SHOW TABLES; or use infomration schema: *SELECT * FROM information_schema.TABLES* or *SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.TABLES* or *SELECT DISTINCT TABLE_SCHEMA,TABLE_NAME FROM information_schema.COLUMNS* * * b: produce the result of all the columns with the specified database table * SELECT * FROM `database_name`.`table_name` LIMIT 0? (and fetch fields names via php functions of result set) or SHOW FIELDS FROM `database_name`.`table_name`; or *SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=database_name AND TABLE_NAME=table_name; (and interact as a result set)* * I don't want any other details than these names. I am trying to build a WHILE loop that in pseudo code will look roughly like - query to get a list of all the tables within a database - start of a foreach loop (going one table at a time - query to get all the column names within the table - start of a foreach loop to display column names - display column name - end of foreach loop -end of foreach loop Ron this sounds like a table structure dump to execute a diff, i'm right? -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
[PHP-DB] Calculating Past Dates In A Different Time Zone
Hi Everyone
I need help knowing how to calculate midnight “X” days ago in whatever time
zone the user has selected.
- The context of this is creating a report
- An Example: I am want to base the report on records created between
2013-11-01 00:00:00 and 2013-11-07 23:59:59 in the users time zone
The report I am generating is based on date – time in the column
“occurance_date”. This column is type “datetime”. All dates – times in this
column are in GMT time zone.
I want to calculate the starting and ending dates – times using PHP (Since the
user selects their time zone based on the available PHP time zone list) and
then bind them to the database query:
===
SELECT * FROM `journal_entry` WHERE `occurance_date` BETWEEN :starting_date AND
:ending_date ORDER BY `occurance_date` ASC
===
I normally use the following code to convert between time zones. But I don’t
know how to calculate what time it is in GMT time zone when it is midnight in
the users time zone X days ago, or midnight on November 1st 2013 in the users
time zone.
===
$date = new \DateTime( date('Y-m-d H:i:s') , new \DateTimeZone(
'Canada/Eastern' ));
$date-setTimezone(new \DateTimeZone( $_SESSION['logged_in_timezone'] ));
$starting_date = $date-format('Y-m-d H:i:s');
===
Some reports I am going to be creating will be:
- last 7 days
- last 14 days
I also need to be able to do reports which are based on:
- The month of November 2013
- The last 3 months
I hope this paints a clear picture of what I am working on. If you need
clarification please ask me. Thank you for helping me. Ron
Ron Piggott
www.TheVerseOfTheDay.info
Re: [PHP-DB] Calculating Past Dates In A Different Time Zone
A suggestion I was given is to use the mySQL CONVERT_TZ command with the PHP time zone names. But when I do: SELECT CONVERT_TZ( `journal_entry`.`occurance_date` , 'GMT', 'America/Bahia' ) FROM `journal_entry` I am receiving NULL as the resulting date. Does mySQL accept PHP time zone names? Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Saving Image in mySQL
Hi All
I don’t understand how to save an image to a mySQL table based on the following
form. I am trying to do this using Prepared Statements. All the fields except
the image file itself save in the database. Right now I have $file as the
variable when binding the values. What should it be? Ron
===
form action=add_my_image.php method=post
enctype=multipart/form-data
label for=fileFilename:/label
input type=file name=filebr
input type=submit name=submit value=submit
/form
===
mySQL table structure:
===
reference – INT 10 auto_increment primary
caption – VARCHAR 250
image_type – VARCHAR 100
image_size – INT 10
image_name – VARCHAR 100
image – LONGBLOB
===
Prepared Statement:
===
# mySQL query
$query = INSERT INTO `my_images` ( `reference` , `caption` , `image_type`
, `image_size` , `image_name` , `image` ) VALUES ( NULL , :caption ,
:image_type , :image_size , :image_name , :image );;
# apply query to Prepared Statement
if($stmt = $dbh-prepare( $query )) {
# bind variables
$stmt-bindValue(':caption', 'Test Caption', PDO::PARAM_STR);
$stmt-bindValue(':image_type', $_FILES[file][type],
PDO::PARAM_STR);
$stmt-bindValue(':image_size', $_FILES[file][size],
PDO::PARAM_INT);
$stmt-bindValue(':image_name', $_FILES[file][name],
PDO::PARAM_STR);
$stmt-bindValue(':image', $file, PDO::PARAM_STR);
# execute query
if ( $stmt-execute() or die(print_r($stmt-errorInfo(), true)) ) {
# retrieve auto_increment value
$new_record_reference = $dbh-lastInsertId();
}
}
===
Ron Piggott
www.TheVerseOfTheDay.info
[PHP-DB] Processing Account Login
Hi Everyone When I login to PayPal a total of 3 web pages are used: 1 – A screen for the user to type in their user name password 2 – A screen saying “ Logging in “* 3 – My Welcome / Personalized Account Summary Screen * During the 2nd web page my PHP script is going to be accessing the mySQL database to check the login credentials. - I would like to know how this is done - I would like to know how to re-direct the user to a “incorrect password” screen if this is the case - I would like to know how to re-direct to a “security question” screen as a secondary step for those users who want this additional security (such as what I am offered through my online banking sign in process) I don’t know what an effective search query is on Google. I don’t mind (nor will I take offense) on being directed to a tutorial. I want to ensure what I am putting into production is high quality and not hap hazard. Thank you for any help you are able to provide me with. Ron Piggott www.TheVerseOfTheDay.info
[PHP-DB] Quotation marks in HTML form values
I have setup the following echo after a database query: ?php echo input type=\text\ name=\description\ value=\ . $email_template['description'] . \ class=\contact_center_email_template_maintenance_user_input_data\ /\r\n; ? What I don’t understand is what to do in the event the variable $email_template['description'] retrieved in the database query contains a quotation mark “ --- In this event only the word “current” populates the “description” field input type=text name=description value=current Verse of the Day daily devotion e-mail template class=contact_center_email_template_maintenance_user_input_data / Ron Piggott www.TheVerseOfTheDay.info
[PHP-DB] Multiple Database Connection Using Prepard Statements
How do I connect to multiple mySQL databases using Prepared Statements ? I have the syntax === $dsh = 'mysql:host=localhost;dbname='.$database3; $dbh = new PDO($dsh, $username, $password); === I want to connect to $database1 without loosing my $database3 connection Thoughts? Comments? Ron Piggott www.TheVerseOfTheDay.info
[PHP-DB] Subscription Suspensions
Hi Everyone. I manage an e-mail list. I am trying to add a new function into it: suspensions. The idea is that someone wouldn’t have to unsubscribe. Instead there would be a record of their suspension in the table member_subscription_suspensions and their account wouldn’t be included as a database query result when sending out the daily e-mail. There are three tables: Profile of the subscriber, subscriptions and subscription suspensions: Membership Profiles are in this table: CREATE TABLE IF NOT EXISTS `member` ( `record` int(10) NOT NULL AUTO_INCREMENT, `first_name` varchar(40) NOT NULL DEFAULT '', `last_name` varchar(40) NOT NULL DEFAULT '', `email` varchar(40) NOT NULL DEFAULT '', PRIMARY KEY (`record`), UNIQUE KEY `email` (`email`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3861 ; Subscriptions are stored in this table: CREATE TABLE IF NOT EXISTS `member_subscriptions` ( `subscription_reference` int(30) NOT NULL AUTO_INCREMENT, `member_reference` int(10) NOT NULL DEFAULT '0', `list` int(2) NOT NULL DEFAULT '0', PRIMARY KEY (`subscription_reference`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2130 ; I have designed this table to store subscription suspensions: CREATE TABLE IF NOT EXISTS `member_subscription_suspensions` ( `reference` int(30) NOT NULL AUTO_INCREMENT, `member_reference` int(11) NOT NULL DEFAULT '0', `list` int(2) NOT NULL DEFAULT '0', `subscription_begins` date NOT NULL DEFAULT '-00-00', `subscription_expires` date NOT NULL DEFAULT '-00-00', PRIMARY KEY (`reference`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; The common column between each table is: member.record member_subscriptions.member_reference member_subscription_suspensions.member_reference The only way I know how to write the query (which is wrong) is: SELECT * FROM ( `member` INNER JOIN `member_subscriptions` ON `member`.`record` = `member_subscriptions`.`member_reference` ) LEFT OUTER JOIN `member_subscription_suspensions` ON `member`.`record` = `member_subscription_suspensions`.`member_reference` WHERE CURDATE() BETWEEN `member_subscription_suspensions`.`subscription_begins` AND `member_subscription_suspensions`.`subscription_expires` AND `member_subscriptions`.`list` = 1 How do I change this query to exclude any one who has a suspension record for “today”. IE CURDATE() Thanks for your help. Ron Ron Piggott www.TheVerseOfTheDay.info
Re: [PHP-DB] Subscription Suspensions
On Tue, Jan 31, 2012 at 6:35 PM, Ron Piggott ron.pigg...@actsministries.org wrote: Hi Everyone. I manage an e-mail list. I am trying to add a new function into it: suspensions. The idea is that someone wouldn’t have to unsubscribe. Instead there would be a record of their suspension in the table member_subscription_suspensions and their account wouldn’t be included as a database query result when sending out the daily e-mail. There are three tables: Profile of the subscriber, subscriptions and subscription suspensions: How do I change this query to exclude any one who has a suspension record for “today”. IE CURDATE() Thanks for your help. Ron Ron Piggott Hi Ron, I'm not sure why you posted your question here, it has no relation to PHP whatsoever. And while you're not specifically asking, you cannot assume people will start to write SQL for you. It would've made sense if you mentioned what database you're using. Some databases support the EXCEPT operator in SQL, though it seems that the most popular one, MySQL, does not support it. You can achieve the same with LEFT JOIN. Wikipedia has an example[1] on how to replace the EXCEPT operator on databases that don't support it. Hope this will help you get started. - Matijn [1] http://en.wikipedia.org/wiki/Set_operations_(SQL)#EXCEPT_operator Thank you for showing me this web page. It was what I was trying to find. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Prepared Statements
I have two questions about Prepared Statements from the code below:
#1:
The purpose of the first $query is to determine if the authorization code
supplied is both accurate and also live
- When the authorization code is generated it is given a time frame when it
must be used in. The columns `start_date` and `end_date` are both DATETIME.
This is why I am using NOW() to check the “shelf life”.
My question: Is there a better way to confirm the record was found than using:
if ( $row['authorization_code'] == $authorization_code ) {
In another way of executing a mySQL database query using PHP I can count the #
rows the result brought with the command “mysql_numrows”. I don’t know how to
do this in Prepared Statements. I wonder if comparing the # of rows found is a
better method? or what other programmers are using?
#2:
How can I tell if the UPDATE $query executed successfully? I am wanting to do
something like:
echo “Update Successful – No changes were required during this review”;
if the UPDATE is successful --- otherwise I need to direct the user to try
again with a different message:
echo “Update was unsuccessful – Follow this link to try again”;
I am still getting use to Prepared Statements, this is why I am asking these
questions --- Thank you for helping me. Ron
===
?php
$dsh = 'mysql:host=localhost;dbname='.$database;
$dbh = new PDO($dsh, $username, $password);
#query for the authorization code
$query = SELECT `authorization_code` FROM
`directory_listing_update_authorizations` WHERE NOW() BETWEEN `start_date` AND
`end_date` AND `authorization_code` = :authorization_code AND
`directory_entries_reference` = :directory_entries_reference LIMIT 1;;
$stmt = $dbh-prepare($query);
$stmt-bindValue(':directory_entries_reference', $directory_entries_reference,
PDO::PARAM_STR);
$stmt-bindValue(':authorization_code', $authorization_code, PDO::PARAM_STR);
$stmt-execute() or die(print_r($stmt-errorInfo(), true));
while ($row = $stmt-fetch()) {
if ( $row['authorization_code'] == $authorization_code ) {
#update directory_entries.last_review with today's date
$query = UPDATE `directory_entries` SET `last_review` = NOW() WHERE
`reference` = :directory_entries_reference LIMIT 1;;
$stmt = $dbh-prepare($query);
$stmt-bindValue(':directory_entries_reference',
$directory_entries_reference, PDO::PARAM_STR);
$stmt-execute() or die(print_r($stmt-errorInfo(), true));
} else {
#failure, direct user to request new authorization code or login
manually
}
}
www.TheVerseOfTheDay.info
Re: [PHP-DB] Exporting mySQL to Excel
I have tried to implement a CSV solution. Following a CSV header each field is displayed using this syntax: (Except the last doesn't end with a , but \r\n ) echo \ . trim( str_replace( '' , '' , stripslashes( mysql_result($listing_result , $i , ministry_profiles.address_line_1) ) ) ) . \ , ; I have these 2 problems: - Excel isn't providing a popup screen for me to specify the content of each field is contained within a pair of and , is used to separate each field. - Additionally Excel is interpreting a comma as the start of a new field. This is problematic in fields where a comma legitimately exists in the middle of a field. I am wondering how phpmyadmin makes Excel files on the fly --- Is it a class? I am unsure how to proceed. Ron Piggott www.TheVerseOfTheDay.info -Original Message- From: Jimi Thompson Sent: Tuesday, November 08, 2011 4:59 PM To: Ron Piggott Cc: php-db@lists.php.net Subject: Re: [PHP-DB] Exporting mySQL to Excel The best option I've found is to write to a file that is then sent to the browser for download. Are there others? Yes but that's the one I've had the best experience with. It seems to work reliably regardless of browser or version of Office or any thing else. HTH! On Tue, 2011-11-08 at 13:31 -0500, Ron Piggott wrote: What is the preferred method used to export mySQL to Excel within the context of PHP? I have looked on Google and found a wide variety of options. Ron Ron Piggott www.TheVerseOfTheDay.info -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Exporting mySQL to Excel
What is the preferred method used to export mySQL to Excel within the context of PHP? I have looked on Google and found a wide variety of options. Ron Ron Piggott www.TheVerseOfTheDay.info
Re: [PHP-DB] Exporting mySQL to Excel
Trying to setup so within the administration screen the users may download the database. Ron Piggott www.TheVerseOfTheDay.info -Original Message- From: Bastien Koert Sent: Tuesday, November 08, 2011 1:33 PM To: Ron Piggott Cc: php-db@lists.php.net Subject: Re: [PHP-DB] Exporting mySQL to Excel On Tue, Nov 8, 2011 at 1:33 PM, Bastien Koert phps...@gmail.com wrote: On Tue, Nov 8, 2011 at 1:31 PM, Ron Piggott ron.pigg...@actsministries.org wrote: What is the preferred method used to export mySQL to Excel within the context of PHP? I have looked on Google and found a wide variety of options. Ron Ron Piggott www.TheVerseOfTheDay.info phpmyadmin supports this easily -- Bastien Cat, the other other white meat or if you are creating some report, i have used both CSV files and PHP EXCEL to create the output -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SELECT
I need help creating a mySQL query that will select the correct introduction message for a website I am making. The way I have designed the table I can’t wrap my mind around the SELECT query that will deal with the day # of the month. The part of the SELECT syntax I am struggling with is when the introduction message is to change mid month. The reason I am struggling with this is because I haven’t used ‘DATE’ for the column type. The reason I didn’t use ‘DATE’ is because the same message will be displayed year after year, depending on the date range. What I am storing in the table is the start month # (1 to 12) and day # (1 to 31) and then the finishing month # (1 to 12) and the finishing day # (1 to 31) Table structure for table `introduction_messages` -- CREATE TABLE IF NOT EXISTS `introduction_messages` ( `reference` int(2) NOT NULL AUTO_INCREMENT, `start_month` int(2) NOT NULL, `start_day` int(2) NOT NULL, `end_month` int(2) NOT NULL, `end_day` int(2) NOT NULL, `theme` varchar(100) NOT NULL, `message` longtext NOT NULL, PRIMARY KEY (`reference`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ; My query so far is: SELECT * FROM `introduction_messages` WHERE 11 BETWEEN `start_month` AND `end_month` 11 is for November. 2 rows have been selected: Row #1: `start_month` 9 `start_day` 16 `end_month` 11 `end_day` 15 Row #2: `start_month` 11 `start_day` 16 `end_month` 12 `end_day` 10 How do I modify the query to incorporate the day #? Ron www.TheVerseOfTheDay.info
[PHP-DB] SELECT syntax
In my Bible_Trivia table I have the columns `trivia_answer_1`, `trivia_answer_2`, `trivia_answer_3`, `trivia_answer_4`, `answer` `answer` is an integer always with a value of 1 to 4. Is there a way to use the value of `answer` to only select the correct trivia answer? This doesn’t work, but this is the idea I am trying to achieve: SELECT `trivia_answer_`answer`` FROM `Bible_trivia` Thanks in advance, Ron www.TheVerseOfTheDay.info
[PHP-DB] Prepared Statements With Multiple Databases
I need my Prepared Statement database connection to be able to connect with two different databases (which use the same username / password). They are assigned variables $database1 and $database2 What I have been using so far is: $dsh = 'mysql:host=localhost;dbname='. $database1; $dbh = new PDO($dsh, $username, $password); Is there a way to amend this with a second database connection? Ron www.TheVerseOfTheDay.info
Re: [PHP-DB] SELECT online store discount %
A variety of if’s and Greatest in conjunction to mySQL math works! SELECT ( ( GREATEST( IF( ( SELECT 10 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` = DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `buyer_email` = '$client_email' AND `paymentstatus` = 'Completed' LIMIT 1 ) , 10, 0 ) , IF( ( SELECT 5 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` BETWEEN DATE_SUB( NOW( ) , INTERVAL 1 YEAR ) AND DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `buyer_email` = '$client_email' AND `paymentstatus` = 'Completed' LIMIT 1 ) , 5, 0 ) ) ) + ( IF( ( SELECT 10 AS discount FROM `subscriber_details` WHERE `email` = '$client_email' LIMIT 1 ), 10, 0 ) ) ) AS discount_percentage The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info From: Amit Tandon Sent: Monday, August 22, 2011 5:45 AM To: Ron Piggott Cc: php-db@lists.php.net Subject: Re: [PHP-DB] SELECT online store discount % Ron Have u thought of CASE (in SELECT). Remebber their is some syntactical difference in CASE for SELECT and CASE in procedures regds amit The difference between fiction and reality? Fiction has to make sense. On Sun, Aug 21, 2011 at 12:55 AM, Ron Piggott ron.pigg...@actsministries.org wrote: I am trying to write a database query that determine the customer loyalty discount for an online store. I am wondering if there is a way of doing this as 1 query, instead of multiple and using PHP to do the math? - I want to offer a 10% discount if the person is a subscriber SELECT 10 AS discount FROM `subscriber_details` WHERE `email` = '$client_email' LIMIT 1 - I also want to offer a customer loyalty discount: 10% if this is a purchase within 4 months of the previous purchase, SELECT 10 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` = DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `paymentstatus` = 'Completed' LIMIT 1 - OR 5% if the most recent previous purchase is between 4 months and 1 year ago. SELECT 5 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` BETWEEN DATE_SUB( NOW( ) , INTERVAL 1 YEAR ) AND DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `paymentstatus` = 'Completed' LIMIT 1 The discounts possibilities would be: - 20% (a subscriber with a purchase within the past 4 months) - 15% (a subscriber with a purchase between 4 months and a year ago) - 10% (for being a subscriber) - 10% (for a purchase made within the past 4 months) - 5% (for a purchase made between 4 months and a year ago) Is there a way to do this all within the context of 1 query? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] SELECT online store discount %
I am trying to write a database query that determine the customer loyalty discount for an online store. I am wondering if there is a way of doing this as 1 query, instead of multiple and using PHP to do the math? - I want to offer a 10% discount if the person is a subscriber SELECT 10 AS discount FROM `subscriber_details` WHERE `email` = '$client_email' LIMIT 1 - I also want to offer a customer loyalty discount: 10% if this is a purchase within 4 months of the previous purchase, SELECT 10 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` = DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `paymentstatus` = 'Completed' LIMIT 1 - OR 5% if the most recent previous purchase is between 4 months and 1 year ago. SELECT 5 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` BETWEEN DATE_SUB( NOW( ) , INTERVAL 1 YEAR ) AND DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `paymentstatus` = 'Completed' LIMIT 1 The discounts possibilities would be: - 20% (a subscriber with a purchase within the past 4 months) - 15% (a subscriber with a purchase between 4 months and a year ago) - 10% (for being a subscriber) - 10% (for a purchase made within the past 4 months) - 5% (for a purchase made between 4 months and a year ago) Is there a way to do this all within the context of 1 query? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] CURDATE()
I am setting up a daily cron job to update the site map on my web site. I want to delete any records that weren’t updated by the cron job each day. The way I can distinguish this is with the timestamp column named “last_record_update” If a record wasn’t updated it is no longer part of the web site. I am trying to figure out if there is a way for me to use mysql’s date functions to query the records that are no longer part of the web site. What I tried below doesn’t work: ( CURDATE() . % ) The reason I wanted to use % is because the time will follow the date in a “timestamp” column Is there a similar way to do what I am trying: SELECT `reference` FROM `sitemap_pages` WHERE `last_record_update` NOT LIKE ( CURDATE() . % ) ORDER BY `reference` +0 Thanks for helping. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] UNION ALL
I am trying to setup a cross reference database query for the first time. What I am trying to do is have a table with the structure: CREATE TABLE IF NOT EXISTS `frequently_accessed_passages_of_scripture_cross_reference` ( `reference` int(5) NOT NULL AUTO_INCREMENT, `cross_reference_article_1` int(3) NOT NULL, `cross_reference_article_2` int(3) NOT NULL, PRIMARY KEY (`reference`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; If the content theme (based on auto_increment values) is listed in either cross_reference_article_1 OR cross_reference_article_2 then the counter part article will be a result so I may offer an “Also available:” type link on the web page. My attempt at the UNION ALL query is below. I am receiving the error “Every derived table must have its own alias” I am unsure of how to interpret this and correct the situation. Thanks for your help. Ron SELECT cross_reference_article_2 FROM ( SELECT `cross_reference_article_2` FROM `frequently_accessed_passages_of_scripture_cross_reference` WHERE `cross_reference_article_1` = 1 ) UNION ALL ( SELECT `cross_reference_article_1` FROM `frequently_accessed_passages_of_scripture_cross_reference` WHERE `cross_reference_article_2` = 1 ) AS cross_reference_view_result The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] Parsing words
Hi Everyone. Using the following query: SELECT `topic` FROM `frequently_accessed_passages_of_scripture` WHERE `frequently_accessed_passages_of_scripture_theme_reference` = 1 I would like to parse all the words out of the column topic that are 4 characters long or more, in alphabetical order, separated by commas, removing all duplicate words. My desired output is: $topic = flower, garden, grass, plant; Does mySQL have the capacity to do this? (If the query caused the result to be in an alias table so that is one word per line I could work with this adding the commas) Or do I need to do this within PHP? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] Re: mysql COUNT row results
-Original Message- From: Geoff Lane Sent: Thursday, June 23, 2011 2:15 AM To: Ron Piggott Cc: php-db@lists.php.net Subject: Re: mysql COUNT row results Hi Ron, On Thursday, June 23, 2011, 6:14:38 AM, you wrote: Is there a way that SELECT COUNT(auto_increment) as total_subscribers , `email` FROM `table` may exist within the same query and provide more than 1 row of search results? When I run a query like this the COUNT portion of the result is allowing only 1 to be selected. My desire is to have the the COUNT result appended to each row. Thoughts anyone? Ron As you noticed, the aggregate function COUNT() means that you get a table with only one row, so you need to join that table to the one that it aggregates. So something like: SELECT A.total_subscribers, B.email FROM table B, (SELECT COUNT(auto_increment) AS total_subscribers FROM table) AS A Should do the trick. HTH, Geoff thank you so much. This worked. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] mysql COUNT row results
Is there a way that SELECT COUNT(auto_increment) as total_subscribers , `email` FROM `table` may exist within the same query and provide more than 1 row of search results? When I run a query like this the COUNT portion of the result is allowing only 1 to be selected. My desire is to have the the COUNT result appended to each row. Thoughts anyone? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] Re: Help with AVG()
I have continued to work on the average query I need help. I am still needing help. What I don’t understand is why `bible_anagrams_rss_feed`.`rss_feed_date` BETWEEN `bible_anagrams_rss_feed`.`rss_feed_date` AND DATE_SUB( `bible_anagrams_rss_feed`.`rss_feed_date` , INTERVAL -1 WEEK ) won’t isn’t selecting the 7 consecutive dates leading up to and including the date of `bible_anagrams_rss_feed`.`rss_feed_date` I am only getting the value of `bible_anagrams`.`views` from the current date as the value of average_views in the query: SELECT `bible_anagrams_rss_feed`.`rss_feed_date` , SUM( `bible_anagrams`.`views` ) AS total_views , SUM( `bible_anagrams`.`views` ) / COUNT(`bible_anagrams_rss_feed`.`reference`) AS average_views FROM `bible_anagrams_rss_feed` INNER JOIN `bible_anagrams` ON `bible_anagrams_rss_feed`.`bible_anagrams_reference` = `bible_anagrams`.`reference` WHERE `bible_anagrams_rss_feed`.`rss_feed_date` BETWEEN `bible_anagrams_rss_feed`.`rss_feed_date` AND DATE_SUB( `bible_anagrams_rss_feed`.`rss_feed_date` , INTERVAL -1 WEEK ) GROUP BY `bible_anagrams_rss_feed`.`rss_feed_date` ORDER BY `bible_anagrams_rss_feed`.`rss_feed_date` DESC rss_feed_date Descending total_views average_views 2011-06-13 12:00:02 13 13. 2011-06-12 12:00:01 10 10. 2011-06-11 12:00:02 18 18. 2011-06-10 12:00:02 14 14. 2011-06-09 12:00:01 20 20. 2011-06-08 12:00:01 28 28. 2011-06-07 12:00:03 15 15. 2011-06-06 12:00:02 21 21. 2011-06-05 12:00:01 20 20. 2011-06-04 12:00:02 33 33. 2011-06-03 12:00:02 23 23. 2011-06-02 12:00:02 25 25. 2011-06-01 12:00:01 13 13. 2011-05-31 12:00:02 41 41. 2011-05-30 12:00:01 27 27. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] Help with AVG()
Hi Everyone I am trying to figure out how to write a SELECT query that will give me the average of `bible_anagrams`.`views` starting on the date specified in `bible_anagrams_rss_feed`.`rss_feed_date` and the previous 6 calendar days (for a total of 7 days). What I am trying to figure out is the average of how many times the anagrams RSS Feed were accessed between June 1st and 7th, June 2nd and 8th, June 3rd and 9th, etc. - There is 1 row for each date in the table bible_anagrams_rss_feed. I tried the following syntax, but it is giving me an overall average, not by the date ranges: SELECT AVG(`bible_anagrams`.`views`) AS average_views FROM `bible_anagrams` INNER JOIN `bible_anagrams_rss_feed` ON `bible_anagrams`.`reference` = `bible_anagrams_rss_feed`.`bible_anagrams_reference` ORDER BY `bible_anagrams_rss_feed`.`rss_feed_date` DESC LIMIT 7 I am wanting the query I am asking help for to be included as one of the mySQL results, where I have indicated “AVERAGE VIEWS QUERY HERE” (although if there is a better way I am opening to learning it): SELECT `bible_anagrams_rss_feed`.`rss_feed_date` , `bible_anagrams`.`reference`, `bible_anagrams`.`bible_anagram_word` , `bible_anagrams`.`views` , ( AVERAGE VIEWS QUERY HERE ) AS average_views FROM `bible_anagrams_rss_feed` INNER JOIN `bible_anagrams` ON `bible_anagrams`.`reference` = `bible_anagrams_rss_feed`.`bible_anagrams_reference` ORDER BY `bible_anagrams_rss_feed`.`rss_feed_date` DESC Thanks for helping me, Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] mysqldump permissions
I created a VIEW “table” yesterday. Last night when the cron job ran to back up the database I received this error: mysqldump: Couldn't execute 'show create table `bible_anagrams_rss_feed_summary`': SHOW VIEW command denied to user I use the following syntax to do the backup of the database each day within the context of a cron job: date=`date -I` ; mysqldump –uUSER –pPASS --all-databases | gzip /home/path/backup_$date.sql.gz Even when I have given the mysql backup user all the permissions available within the control panel of my web site hosting company I still get the same error. These are the permissions I have available. SELECT INSERT UPDATE DELETE INDEX CREATE TEMPORARY TABLE EXECUTE CREATE ALTER DROP LOCK TABLES REFERENCES CREATE ROUTINE How to resolve the error? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] Prepared Statements Rows Selected
What command will tell me the # of rows the SELECT query retrieved using
Prepared Statements.
$dsh = 'mysql:host=localhost;dbname='.$database;
$dbh = new PDO($dsh, $username, $password);
$stmt = $dbh-prepare($query);
$stmt-bindParam(':email', $email);
$stmt-bindParam(':pass', $pass);
$stmt-execute();
I am looking for the equivalent of mysql_numrows
mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( Unable to select database);
$result=mysql_query($query);
$num=mysql_numrows($result);
Ron
The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info
[PHP-DB] Re: GROUP BY
I figured out last night’s query and it’s a dozy. The sub query SELECT * FROM `verse_of_the_day_Bible_trivia` WHERE `verse_of_the_day_Bible_trivia`.`live` =1 ORDER BY `verse_of_the_day_Bible_trivia`.`date_added` DESC puts the questions into descending order making the INNER JOIN link with the most recently added trivia question in each category. This gives me the desired results, I don't know if there is a way to stream line this or not. SELECT Bible_trivia_category_reference , date_added , question_count , filename , created FROM ( SELECT `verse_of_the_day_Bible_trivia`.`Bible_trivia_category_reference` , `verse_of_the_day_Bible_trivia`.`date_added` , COUNT( `verse_of_the_day_Bible_trivia`.`reference` ) AS question_count, `verse_of_the_day_bible_trivia_ready_made_handouts`.`filename` , `verse_of_the_day_bible_trivia_ready_made_handouts`.`created` FROM ( SELECT * FROM `verse_of_the_day_Bible_trivia` WHERE `verse_of_the_day_Bible_trivia`.`live` =1 ORDER BY `verse_of_the_day_Bible_trivia`.`date_added` DESC ) AS verse_of_the_day_Bible_trivia INNER JOIN `Bible_trivia_category` ON `Bible_trivia_category`.`reference` = `verse_of_the_day_Bible_trivia`.`Bible_trivia_category_reference` LEFT OUTER JOIN `verse_of_the_day_bible_trivia_ready_made_handouts` ON `Bible_trivia_category`.`reference` = `verse_of_the_day_bible_trivia_ready_made_handouts`.`Bible_trivia_category_reference` GROUP BY `Bible_trivia_category`.`reference` HAVING question_count = 10 ORDER BY `verse_of_the_day_Bible_trivia`.`date_added` DESC ) AS bible_trivia_handouts WHERE date_added created ORDER BY Bible_trivia_category_reference ASC The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] GROUP BY
Is there a way in the query below that the “LEFT OUTER JOIN” connects with only the most recently added entry in `verse_of_the_day_Bible_trivia` for each category ( `verse_of_the_day_Bible_trivia`.`Bible_trivia_category_reference` ) based on the column `verse_of_the_day_Bible_trivia`.`date_added` ? The purpose of this query is to compare the most recently added Bible trivia questions ( `verse_of_the_day_Bible_trivia`.`date_added` ) from each category ( `Bible_trivia_category`.`reference` ) with the last time the category handout was created ( `verse_of_the_day_bible_trivia_ready_made_handouts`.`created` ). If there are new questions since the last time the handout was created ( `verse_of_the_day_Bible_trivia`.`date_added` ) OR the category now has 10 or more questions then the handout will be re-created (through a cron job) based on the results of this query. The HAVING condition is to eliminate categories with less than 10 questions. - See table structures below Thank you for your help. Ron === SELECT `verse_of_the_day_Bible_trivia`.`Bible_trivia_category_reference` , `verse_of_the_day_Bible_trivia`.`date_added` , COUNT( `verse_of_the_day_Bible_trivia`.`reference` ) AS question_count, `verse_of_the_day_bible_trivia_ready_made_handouts`.`filename` FROM ( `verse_of_the_day_Bible_trivia` INNER JOIN `Bible_trivia_category` ON `Bible_trivia_category`.`reference` = `verse_of_the_day_Bible_trivia`.`Bible_trivia_category_reference` ) LEFT OUTER JOIN `verse_of_the_day_bible_trivia_ready_made_handouts` ON `Bible_trivia_category`.`reference` = `verse_of_the_day_bible_trivia_ready_made_handouts`.`Bible_trivia_category_reference` WHERE `verse_of_the_day_Bible_trivia`.`live` = 1 AND `verse_of_the_day_Bible_trivia`.`date_added` `verse_of_the_day_bible_trivia_ready_made_handouts`.`created` GROUP BY `Bible_trivia_category`.`reference` HAVING question_count =10 ORDER BY `verse_of_the_day_Bible_trivia`.`reference` ASC === `Bible_trivia_category` CREATE TABLE IF NOT EXISTS `Bible_trivia_category` ( `reference` int(3) NOT NULL AUTO_INCREMENT, `category` varchar(45) NOT NULL, PRIMARY KEY (`reference`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=35 ; `verse_of_the_day_Bible_trivia` CREATE TABLE IF NOT EXISTS `verse_of_the_day_Bible_trivia` ( `reference` int(5) NOT NULL AUTO_INCREMENT, `Bible_trivia_category_reference` int(3) NOT NULL DEFAULT '0', `trivia_question` varchar(300) NOT NULL, `trivia_answer_1` varchar(150) NOT NULL, `trivia_answer_2` varchar(150) NOT NULL, `trivia_answer_3` varchar(150) DEFAULT NULL, `trivia_answer_4` varchar(150) DEFAULT NULL, `answer` int(1) NOT NULL DEFAULT '0', `explanation` varchar(1000) DEFAULT NULL, `Bible_verse_reference` varchar(60) DEFAULT NULL, `seasonal_use` int(1) NOT NULL DEFAULT '0', `date_added` datetime NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `assigned_date` date NOT NULL DEFAULT '-00-00', `store_catalog_reference` int(3) NOT NULL DEFAULT '0', `teaching_devotional_messages_reference` int(3) NOT NULL DEFAULT '0', `live` int(1) NOT NULL DEFAULT '0', `user_hits` int(25) NOT NULL DEFAULT '0', `user_hits_answer` int(25) NOT NULL DEFAULT '0', PRIMARY KEY (`reference`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=410 ; `verse_of_the_day_bible_trivia_ready_made_handouts` CREATE TABLE IF NOT EXISTS `verse_of_the_day_bible_trivia_ready_made_handouts` ( `reference` int(5) NOT NULL AUTO_INCREMENT, `Bible_trivia_category_reference` int(3) NOT NULL, `filename` varchar(100) NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `live` int(1) NOT NULL, `views` int(25) NOT NULL, PRIMARY KEY (`reference`), UNIQUE KEY `verse_of_the_day_Bible_trivia_reference` (`Bible_trivia_category_reference`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ; The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] COUNT and OUTER JOIN results
The following query returns all 8 prayer request categories with the total # of requests every submitted to each category: SELECT `prayer_request_category`.`reference` , `prayer_request_category`.`category` , COUNT( `prayer_requests`.`reference` ) AS category_request_count FROM `prayer_request_category` LEFT OUTER JOIN `prayer_requests` ON `prayer_request_category`.`reference` = `prayer_requests`.`prayer_request_category_reference` GROUP BY `prayer_request_category`.`reference` ORDER BY `prayer_request_category`.`category` ASC I would like to add the following 2 WHERE conditions to this query so only the live prayer requests are included in the COUNT: `prayer_requests`.`approval_level` IN ( 1, 3 ) `prayer_requests`.`prayer_request_type` = 1 When I do this only the categories with live prayer requests are returned, instead of all 8 categories. Is there a way to build these WHERE conditions which will still allow all 8 categories to be included in the result? Thank you, Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] COUNT and OUTER JOIN results
thank you for explaining this to me Amit. It works. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info From: Amit Tandon Sent: Friday, May 06, 2011 5:49 AM To: Ron Piggott Cc: php-db@lists.php.net Subject: Re: [PHP-DB] COUNT and OUTER JOIN results Dear Ron Take your condition to ON cluause. So your on clause (for LEFT JOIN) would read something like ON `prayer_request_category`.` reference` = `prayer_requests`.`prayer_request_category_reference` AND `prayer_requests`.`approval_ level` IN ( 1, 3 ) `prayer_requests`.`prayer_request_type` = 1 regds amit The difference between fiction and reality? Fiction has to make sense. On Fri, May 6, 2011 at 2:42 PM, Ron Piggott ron.pigg...@actsministries.org wrote: The following query returns all 8 prayer request categories with the total # of requests every submitted to each category: SELECT `prayer_request_category`.`reference` , `prayer_request_category`.`category` , COUNT( `prayer_requests`.`reference` ) AS category_request_count FROM `prayer_request_category` LEFT OUTER JOIN `prayer_requests` ON `prayer_request_category`.`reference` = `prayer_requests`.`prayer_request_category_reference` GROUP BY `prayer_request_category`.`reference` ORDER BY `prayer_request_category`.`category` ASC I would like to add the following 2 WHERE conditions to this query so only the live prayer requests are included in the COUNT: `prayer_requests`.`approval_level` IN ( 1, 3 ) `prayer_requests`.`prayer_request_type` = 1 When I do this only the categories with live prayer requests are returned, instead of all 8 categories. Is there a way to build these WHERE conditions which will still allow all 8 categories to be included in the result? Thank you, Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] How do I do math with a UNION ALL
Hi Everyone. I am trying to do a SUM of the impressions and usage columns from 2 different tables using a UNION ALL. I have tested that the individual SELECT queries work. I am unsure of the layout for the UNION ALL. Would you help me tweak this query? I also wanted to ask is usage a reserved word in mySQL? Is this the reason the query fails? Ron === SELECT SUM( impressions ) AS impressions, SUM ( usage ) AS usage FROM ( ( SELECT IF ( SUM( `web_advertisements_our_clients_usage`.`impressions` ) , SUM( `web_advertisements_our_clients_usage`.`impressions` ) , 0 ) AS impressions, IF ( SUM( `web_advertisements_our_clients_usage`.`usage` ) , SUM( `web_advertisements_our_clients_usage`.`usage` ) , 0 ) AS usage FROM `web_advertisements_our_clients_usage` WHERE `web_advertisements_our_clients_reference` = 1 AND `month` = 4 AND `year` = 2011 LIMIT 1 ) UNION ALL ( SELECT `impressions`, `usage` FROM `web_advertisements_our_clients` WHERE `reference` = 1 LIMIT 1 ) ) AS monthly_stats_total; The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: RE：[PHP-DB] How do I do math with a UNION ALL
Thank you for the suggestion. This is the actual query that I implemented: Amazing how this works. Ron SELECT SUM( `impressions_total` ) AS impressions_total , SUM( `usage_total` ) AS usage_total FROM ( ( SELECT IF ( SUM( `web_advertisements_our_clients_usage`.`impressions` ) , SUM( `web_advertisements_our_clients_usage`.`impressions` ) , 0 ) AS impressions_total, IF ( SUM( `web_advertisements_our_clients_usage`.`usage` ) , SUM( `web_advertisements_our_clients_usage`.`usage` ) , 0 ) AS usage_total FROM `web_advertisements_our_clients_usage` WHERE `web_advertisements_our_clients_reference` = $web_advertisements_our_clients_reference AND `month` = $stats_month AND `year` = $stats_year LIMIT 1 ) UNION ALL ( SELECT `impressions` AS impressions_total, `usage` as usage_total FROM `web_advertisements_our_clients` WHERE `reference` = $web_advertisements_our_clients_reference LIMIT 1 ) ) AS monthly_stats_total; The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] ORDER BY relevance DESC query
I am programming the search feature for a directory. I am trying to make the
query display the results in order of relevance (Greatest to least). I thought
I had to perform the query similar to a column being selected in order to sort
the results by relevance. What changes do I need to make to my query for it
to work correctly?
The mySQL query is giving me the error message:
#1241 - Operand should contain 1 column(s)
The FULLTEXT index contains:
organization
address_line_1
address_line_2
city
province_state
postal_zip_code
country
telephone
toll_free
fax
email
website
Presently the mySQL query is:
SELECT `reference`, `organization` ,
( SELECT `reference`, `organization` FROM `ministry_profiles`
WHERE
MATCH( `ministry_profiles`.`organization`,
`ministry_profiles`.`address_line_1`, `ministry_profiles`.`address_line_2`,
`ministry_profiles`.`city`, `ministry_profiles`.`province_state`,
`ministry_profiles`.`postal_zip_code`, `ministry_profiles`.`country`,
`ministry_profiles`.`telephone`, `ministry_profiles`.`toll_free`,
`ministry_profiles`.`fax`, `ministry_profiles`.`email`,
`ministry_profiles`.`website` )
AGAINST
('$search')
AND `live` = 1
) AS relevance
FROM `ministry_profiles`
WHERE
MATCH( `ministry_profiles`.`organization`,
`ministry_profiles`.`address_line_1`, `ministry_profiles`.`address_line_2`,
`ministry_profiles`.`city`, `ministry_profiles`.`province_state`,
`ministry_profiles`.`postal_zip_code`, `ministry_profiles`.`country`,
`ministry_profiles`.`telephone`, `ministry_profiles`.`toll_free`,
`ministry_profiles`.`fax`, `ministry_profiles`.`email`,
`ministry_profiles`.`website` )
AGAINST
('$search')
AND `live` = 1
ORDER BY relevance DESC
[PHP-DB] ucwords for mySQL?
I have found mySQL commands for LCASE and UCASE, but nothing equal to the PHP command “ucwords”. Is there a mysql command or will I need to use PHP to manipulate the strings? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] SELECT WHERE length of content question
Is there a command in mySQL that would allow me to SELECT the rows where the `fax` column is more than 11 characters long? OR Do I need to use PHP to assess this? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] UPDATE query
I am wondering if there is a way to do an UPDATE query where only some of the text changes. The column I need to modify is named “toll_free” What I need to search for is: 800- I need it to replace it with is 1-800- - BUT I don’t want to change instances of 1-800- - I need to leave the rest of the toll free phone number in tact. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
Re: [PHP-DB] UPDATE query
I was wondering this Bastien. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info -Original Message- From: Bastien Koert Sent: Tuesday, March 08, 2011 11:20 AM To: Ron Piggott Cc: php-db@lists.php.net Subject: Re: [PHP-DB] UPDATE query On Tue, Mar 8, 2011 at 11:16 AM, Ron Piggott ron.pigg...@actsministries.org wrote: I am wondering if there is a way to do an UPDATE query where only some of the text changes. The column I need to modify is named “toll_free” What I need to search for is: 800- I need it to replace it with is 1-800- - BUT I don’t want to change instances of 1-800- - I need to leave the rest of the toll free phone number in tact. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info Ron, I would strongly suggest that you be consistent in the data. Pick one version (1-800 or 800-) and stick with it. Its a simple matter to do a one time replace on that field to make them all consistent and from there on your programming logic for the update you want to run is then made much simpler. You can do an update with a LIKE but it may update more than what you want to update table set toll_free = '$some_value' where toll_free like '%800-###-' -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] UPDATE query
I found a way to do this Bastien: UPDATE `database`.`table` SET `toll_free` = CONCAT( '1-', `toll_free` ) WHERE `toll_free` LIKE '866-%' Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info -Original Message- From: Bastien Koert Sent: Tuesday, March 08, 2011 11:20 AM To: Ron Piggott Cc: php-db@lists.php.net Subject: Re: [PHP-DB] UPDATE query On Tue, Mar 8, 2011 at 11:16 AM, Ron Piggott ron.pigg...@actsministries.org wrote: I am wondering if there is a way to do an UPDATE query where only some of the text changes. The column I need to modify is named “toll_free” What I need to search for is: 800- I need it to replace it with is 1-800- - BUT I don’t want to change instances of 1-800- - I need to leave the rest of the toll free phone number in tact. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info Ron, I would strongly suggest that you be consistent in the data. Pick one version (1-800 or 800-) and stick with it. Its a simple matter to do a one time replace on that field to make them all consistent and from there on your programming logic for the update you want to run is then made much simpler. You can do an update with a LIKE but it may update more than what you want to update table set toll_free = '$some_value' where toll_free like '%800-###-' -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Search relevance
I would like to have the search results display in order of relevance --- and
essentially add these two lines to the query.
HAVING relevance 0.2
ORDER BY relevance DESC
However with how I built the database and the search spanning several tables I
am unsure how to do this. Could someone help me please?
Ron
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`,
`ministry_profiles_activity`.`activity`
FROM
(
(
( `ministry_profiles` LEFT OUTER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
LEFT OUTER JOIN `ministry_profiles_activity` ON
`ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_profile_categories` ON
`ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
(
MATCH( `ministry_profiles`.`organization`,
`ministry_profiles`.`address_line_1`, `ministry_profiles`.`address_line_2`,
`ministry_profiles`.`city`, `ministry_profiles`.`province_state`,
`ministry_profiles`.`postal_zip_code`, `ministry_profiles`.`country`,
`ministry_profiles`.`telephone`, `ministry_profiles`.`toll_free`,
`ministry_profiles`.`fax`, `ministry_profiles`.`email`,
`ministry_profiles`.`website` )
AGAINST
('$search')
AND
`ministry_profiles`.`live` =1
)
OR
(
MATCH( `ministry_categories`.`category` )
AGAINST
('$search')
)
OR
(
MATCH( `ministry_profiles_activity`.`activity` )
AGAINST
('$search')
AND `ministry_profiles_activity`.`live` =1
)
OR
(
MATCH( `ministry_profiles_listing_details`.`contact`,
`ministry_profiles_listing_details`.`year_founded`,
`ministry_profiles_listing_details`.`volunteer_opportunities`,
`ministry_profiles_listing_details`.`employment_opportunities`,
`ministry_profiles_listing_details`.`members_of`,
`ministry_profiles_listing_details`.`major_events`,
`ministry_profiles_listing_details`.`associate_member_of`,
`ministry_profiles_listing_details`.`registration_number`,
`ministry_profiles_listing_details`.`fund_raising`,
`ministry_profiles_listing_details`.`accredited_by`,
`ministry_profiles_listing_details`.`facebook`,
`ministry_profiles_listing_details`.`twitter` )
AGAINST
('$search')
AND
`ministry_profiles_listing_details`.`live` =1
)
GROUP BY `ministry_profiles`.`reference`
[PHP-DB] Prepared Statements
I am just starting to use Prepared Statements and am in a learning curve.
I am working on the code below. It is for a directory. This is to select the
listing for updating.
How do I tell if there are more than 1 search result?
Am I correctly retrieving the results, if there is more than 1?
I want to make a WHILE loop to display the search results for the listing the
user is editing. (You can see where I have started the ul ... /ul ... I
want that area in the loop)
Ron
$dbh = new PDO($dsh, $username, $password);
$stmt = $dbh-prepare(SELECT `reference`, `organization`, `city`,
`province_state`, `postal_zip_code`, `country` FROM `ministry_profiles` WHERE (
`reference` = :organization_reference ) OR ( `organization` LIKE
%:organization_name% ) OR ( `telephone` LIKE %:organization_phone% ) OR (
`toll_free` LIKE %:organization_toll_free_phone% ) ORDER BY `organization`
ASC);
$stmt-bindParam(':organization_reference', $organization_reference,
PDO::PARAM_STR);
$stmt-bindParam(':organization_name', $organization_name, PDO::PARAM_STR);
$stmt-bindParam(':organization_phone', $organization_phone, PDO::PARAM_STR);
$stmt-bindParam(':organization_toll_free_phone', $organization_phone,
PDO::PARAM_STR);
$stmt-execute();
$result = $stmt-fetch(PDO::FETCH_ASSOC);
echo ul\r\n;
$search_result_organization_reference = $result['reference'];
$search_result_organization = $result['organization'];
$search_result_city = $result['city'];
$search_result_province_state = $result['province_state'];
$search_result_postal_zip_code = $result['postal_zip_code'];
$search_result_country = $result['country'];
echo listrong . $search_result_organization . /strong (Ref: .
$search_result_organization_reference . )br /\r\n;
echo $search_result_city . , . $search_result_province_state . .
$search_result_country . . $search_result_postal_zip_code . /li\r\n;
echo /ul\r\n;
The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info
[PHP-DB] Leap Year
Does mySQL have the ability to figure out if it is leap year? If so, what is the correct syntax for: AND `day` 366 unless it is leap year Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] RSS Feed
I have a question about the mySQL query below. The purpose is to find the last 15 days Bible reading for an RSS feed. In the table each day’s reading is assigned the day # in the year (between 1 and 365). But if the query is ran on January 6th, for example, it will only find 6 rows in the result. Is there a fancy way to ensure a total of 15 rows are retrieved, even if some of the rows from the end of the year (days 350 to 365) are being displayed, so it is like a loop? (I have a record in the table for day 366 for ‘leap year’ that says the Bible reading starts again tomorrow, for when it is needed every 4th year) Ron === $day_of_year = getdate(); #add 1 because yday starts at 0 $day_of_year = $day_of_year[yday] + 1; $query=SELECT * FROM `$database1`.`friends_of_the_ministry_Bible_reading_plans` WHERE `plan` =1 AND `day` = $day_of_year ORDER BY `day` DESC LIMIT 15; === The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
Re: [PHP-DB] RSS Feed
Bastien I have been wondering about this approach this afternoon, but I don't know how to do it. What is throwing me off is the structure of the database. There is no year in it. Only the column day is a number from 1 to 366 (See table structure, below) I get that you are saying though. If it is between January 1st and 15th the remaining records are in December of the previous year. CREATE TABLE IF NOT EXISTS `friends_of_the_ministry_Bible_reading_plans` ( `reference` int(4) NOT NULL AUTO_INCREMENT, `plan` int(2) NOT NULL DEFAULT '0', `day` int(4) NOT NULL DEFAULT '0', `assigned_reading` varchar(65) NOT NULL DEFAULT '', `content_summary` varchar(500) NOT NULL, PRIMARY KEY (`reference`) ) Are you able to offer me some more help with this table structure? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info -Original Message- From: Bastien Koert Sent: Sunday, January 30, 2011 4:10 PM To: Ron Piggott Cc: php-db@lists.php.net Subject: Re: [PHP-DB] RSS Feed On Sun, Jan 30, 2011 at 2:27 PM, Ron Piggott ron.pigg...@actsministries.org wrote: I have a question about the mySQL query below. The purpose is to find the last 15 days Bible reading for an RSS feed. In the table each day’s reading is assigned the day # in the year (between 1 and 365). But if the query is ran on January 6th, for example, it will only find 6 rows in the result. Is there a fancy way to ensure a total of 15 rows are retrieved, even if some of the rows from the end of the year (days 350 to 365) are being displayed, so it is like a loop? (I have a record in the table for day 366 for ‘leap year’ that says the Bible reading starts again tomorrow, for when it is needed every 4th year) Ron === $day_of_year = getdate(); #add 1 because yday starts at 0 $day_of_year = $day_of_year[yday] + 1; $query=SELECT * FROM `$database1`.`friends_of_the_ministry_Bible_reading_plans` WHERE `plan` =1 AND `day` = $day_of_year ORDER BY `day` DESC LIMIT 15; === The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info Ron, Why not do a mktime for -15 days...that should cover the calendar back into last year www.php.net/mktime -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Top 10 Query
I am trying to write a mySQL query to determine if the current word being displayed in the game is one of the top 10 most popular. I am trying to achieve this by creating a table that tracks how many times each word was accessed. A new row is created for each access to the word. The table structure is as follows: CREATE TABLE IF NOT EXISTS `bible_word_scramble_usage` ( `reference` int(25) NOT NULL AUTO_INCREMENT, `bible_dictionary_reference` int(4) NOT NULL, `ip_address` varchar(20) NOT NULL, `date_accessed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`reference`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=122 ; The following is the SELECT query I need help tweaking. What I am trying to do is select the top 10 most popular words and then use a second select to see if the word being displayed is one of the top 10 (IE using the search results of the 10 top SELECT query). The error this query is currently giving me is: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= `top_ten`.`bible_dictionary_reference` = 1 LIMIT 1' This is the query: SELECT `top_ten`.`bible_dictionary_reference` FROM ( SELECT `bible_dictionary_reference` , COUNT( `reference` ) AS word_usage FROM `bible_word_scramble_usage` GROUP BY `bible_dictionary_reference` ORDER BY word_usage DESC LIMIT 10 ) AS top_ten WHERE = `top_ten`.`bible_dictionary_reference` =1 LIMIT 1 Thank you for helping me. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] Re: Top 10 Query
I had an extra “=” sign by the WHERE, my mistake, I couldn’t see it originally. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info From: Ron Piggott Sent: Saturday, January 08, 2011 7:42 AM To: php-db@lists.php.net Subject: Top 10 Query I am trying to write a mySQL query to determine if the current word being displayed in the game is one of the top 10 most popular. I am trying to achieve this by creating a table that tracks how many times each word was accessed. A new row is created for each access to the word. The table structure is as follows: CREATE TABLE IF NOT EXISTS `bible_word_scramble_usage` ( `reference` int(25) NOT NULL AUTO_INCREMENT, `bible_dictionary_reference` int(4) NOT NULL, `ip_address` varchar(20) NOT NULL, `date_accessed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`reference`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=122 ; The following is the SELECT query I need help tweaking. What I am trying to do is select the top 10 most popular words and then use a second select to see if the word being displayed is one of the top 10 (IE using the search results of the 10 top SELECT query). The error this query is currently giving me is: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= `top_ten`.`bible_dictionary_reference` = 1 LIMIT 1' This is the query: SELECT `top_ten`.`bible_dictionary_reference` FROM ( SELECT `bible_dictionary_reference` , COUNT( `reference` ) AS word_usage FROM `bible_word_scramble_usage` GROUP BY `bible_dictionary_reference` ORDER BY word_usage DESC LIMIT 10 ) AS top_ten WHERE = `top_ten`.`bible_dictionary_reference` =1 LIMIT 1 Thank you for helping me. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] Word Matching Application
I am working on a word matching application. Specifically the user will match
a word with it’s definition. I have made some progress since my last post for
help (2 or 3 days ago).
I need help knowing how to alternate between displaying the word and it’s
explanation:
===
echo p\r\n;
echo span class=\bible_match_up_left\\r\n;
echo WORD\r\n;
echo /span\r\n;
echo span class=\bible_match_up_right\\r\n;
echo EXPLANATION\r\n;
echo /span\r\n;
echo /p\r\n;
===
I only know how to do one array at a time, using FOREACH, like this:
===
echo ul\r\n;
foreach($match_words as $word) {
echo li . $word . /li\r\n;
}
echo /ul\r\n;
===
How do I do both the word and explanation at once?
The following is how I query the database for the words / explanations and
create and shuffle the arrays:
===
$query =
SELECT `reference` , `word` , `explanation`
FROM `Bible_dictionary`
WHERE `live` =1
ORDER BY RAND( )
LIMIT 5
;
$words_match_up_result=mysql_query($query);
$records_found=mysql_numrows($words_match_up_result);
#create array from mySQL query
$words = array();
$explanations = array();
$i=1;
while ( $i = $records_found ) {
$reference = mysql_result($words_match_up_result,($i -1),reference);
$words[$reference] = stripslashes( mysql_result($words_match_up_result,($i
-1),word) );
$explanations[$reference] = stripslashes(
mysql_result($words_match_up_result,($i -1),explanation) );
++$i;
}
#shuffle from PHP web site
function custom_shuffle($my_array = array()) {
$copy = array();
while (count($my_array)) {
// takes a rand array elements by its key
$element = array_rand($my_array);
// assign the array and its value to an another array
$copy[$element] = $my_array[$element];
//delete the element from source array
unset($my_array[$element]);
}
return $copy;
}
$match_words = custom_shuffle($words);
$match_explanations = custom_shuffle($explanations);
===
$reference is not in sequential order. $reference is the auto_increment value
of the `Bible_dictionary` table. It’s significance is for scoring how many
the user got right.
Ron
Re: [PHP-DB] Word Matching Application
This works very well. Thank you for your assistance Dan. Ron
The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info
-Original Message-
From: Daniel Brown
Sent: Wednesday, January 05, 2011 9:34 PM
To: Ron Piggott
Cc: php-db@lists.php.net ; Mike Stowe
Subject: Re: [PHP-DB] Word Matching Application
On Wed, Jan 5, 2011 at 21:23, Ron Piggott
ron.pigg...@actsministries.org wrote:
I only know how to do one array at a time, using FOREACH, like this:
===
echo ul\r\n;
foreach($match_words as $word) {
echo li . $word . /li\r\n;
}
echo /ul\r\n;
===
You could either swap that out for a simple `for` loop or add in
an array_combine() call and sort like so:
foreach ($new_array_name as $word = $explanation)
--
/Daniel P. Brown
Network Infrastructure Manager
Documentation, Webmaster Teams
http://www.php.net/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Word Activity Application
I am working on a word activity --- matching words and their definitions. I want to display 5 words on the left hand side and the 5 definitions on the right hand side. But I want the definitions displayed in a different order than the words so the user submits their answer. Should I use PHP to display the definitions in random order? OR Is there a way do this in mySQL that would mix and match results from different rows? This is the query gives me the 5 results SELECT `reference` , `word` , `explanation` FROM `Bible_dictionary` WHERE `live` =1 ORDER BY RAND( ) LIMIT 5 Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] Re: Word Activity Application
The FOREACH below is giving me the error:
Invalid argument supplied for foreach()
Does anyone understand what I have done to cause this error?
#query for words
$query =
SELECT `reference` , `word` , `explanation`
FROM `Bible_dictionary`
WHERE `live` =1
ORDER BY RAND( )
LIMIT 5
;
$words_match_up_result=mysql_query($query);
$records_found=mysql_numrows($words_match_up_result);
echo $records_found . br; # output is 5
#create array from mySQL query
$words = array();
$explanations = array();
$i=1;
while ( $i = $records_found ) {
$words[$i] = stripslashes( mysql_result($words_match_up_result,($i
-1),word) );
$explanations[$i] = stripslashes( mysql_result($words_match_up_result,($i
-1),explanation) );
++$i;
}
#shuffle arrays
$match_words = shuffle ( $words );
$match_explanations = shuffle ( $explanations );
#display words on the screen
foreach($match_words as $word) {
echo $word . br /\r\n;
}
The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info
From: Ron Piggott
Sent: Sunday, January 02, 2011 5:54 PM
To: php-db@lists.php.net
Subject: Word Activity Application
I am working on a word activity --- matching words and their definitions.
I want to display 5 words on the left hand side and the 5 definitions on the
right hand side. But I want the definitions displayed in a different order
than the words so the user submits their answer.
Should I use PHP to display the definitions in random order? OR Is there a way
do this in mySQL that would mix and match results from different rows? This is
the query gives me the 5 results
SELECT `reference` , `word` , `explanation`
FROM `Bible_dictionary`
WHERE `live` =1
ORDER BY RAND( )
LIMIT 5
Ron
The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info
[PHP-DB] bindParam OR bindValue
The INSERT INTO query successfully executes EXACTLY as it is. The bind’s
aren’t working. :{Variable name] is being saved to the database. What do I
need to change? I am not understanding this yet. Ron
$dsh = 'mysql:host=localhost;dbname='.$database2;
$dbh = new PDO($dsh, $username, $password);
$stmt = $dbh-prepare(INSERT INTO `$database2`.`member` ( `record` ,
`first_name` , `last_name` , `address_1` , `address_2` , `address_3` ,
`address_4` , `address_5` , `email` , `prayer_community_alias` , `birth_month`
, `birth_day` , `pass` , `validated` , `last_login` , `last_activity` ,
`birthday_records` , `greeting_reference` , `registration_ip_address`,
`account_created` , `account_suspended` , `account_closed` , `referral_source`
, `friends_of_ministry_package` , `security_question_1` , `security_answer_1` ,
`security_question_2` , `security_answer_2` , `security_question_3` ,
`security_answer_3` ) VALUES ( NULL , ':f1', ':l1', '', '', '', '', '', ':e1',
'', ':birth_month', ':birth_day', ':validate_password', ':validated',
':last_login', ':last_activity', ':birthday_records', ':greeting_reference',
':registration_ip_address', ':account_created', ':account_suspended',
':account_closed', ':referral_source', ':friends_of_ministry_package',
':security_question_1', '', ':security_question_2', '', ':security_question_3',
'' ) ON DUPLICATE KEY UPDATE `validated` = ':validated', `pass` =
':validate_password', `account_suspended` = ':account_suspended',
`account_closed` = ':account_closed', `last_activity` = ':last_activity',
`registration_ip_address` = ':registration_ip_address';);
$stmt-bindParam(':f1', $f1);
$stmt-bindParam(':l1', $l1);
$stmt-bindParam(':e1', $e1);
$stmt-bindValue(':birth_month', '0');
$stmt-bindValue(':birth_day', '0');
$stmt-bindParam(':validate_password', $validate_password);
$stmt-bindValue(':validated', '5');
$stmt-bindParam(':last_login', $todays_date);
$stmt-bindParam(':last_activity', $todays_date);
$stmt-bindValue(':birthday_records', '15');
$stmt-bindValue(':security_question_1', '0');
$stmt-bindValue(':greeting_reference', '0');
$stmt-bindParam(':registration_ip_address', $registration_ip_address);
$stmt-bindParam(':account_created', $todays_date);
$stmt-bindValue(':account_suspended', '-00-00');
$stmt-bindValue(':account_closed', '-00-00');
$stmt-bindValue(':referral_source', 2);
$stmt-bindValue(':friends_of_ministry_package', '0');
$stmt-bindValue(':security_question_1', '0');
$stmt-bindValue(':security_question_2', '0');
$stmt-bindValue(':security_question_3', '0');
// insert one row
$stmt-execute();
#$stmt-close($PDO);
unset($dbh);
The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info
[PHP-DB] Prepared Statements and mySQL
I am trying to implement Prepared Statements on my web site and this is the
first time I have ever used this.
#1) I received the error “class mysql not defined”. That is a reference to the
first line of code (below) where the database connection is established.
#2) How do I assign the auto_increment value to a variable use Prepared
Statements? In the syntax I am attempting below “record” is an auto_increment
column.
#3) Do syntaxes such as “$stmt-bindParam(':account_suspended', -00-00);”
require the date -00-00 to be surrounded by ‘ ?
Thank you to the many of you who have supported me this year when I have had
questions. I am physically handicapped, although I don’t want to make a big
deal about it. I have appreciated the opportunity to continue developing my
PHP / mySQL programming skills in 2010. The Internet is a life line to me.
Ron
$dbh = new mysql('localhost', '$username', '$password', '$database2');
$stmt = $dbh-prepare(INSERT INTO `$database2`.`member` ( `record` ,
`first_name` , `last_name` , `address_1` , `address_2` , `address_3` ,
`address_4` , `address_5` , `email` , `prayer_community_alias` , `birth_month`
, `birth_day` , `pass` , `validated` , `last_login` , `last_activity` ,
`birthday_records` , `greeting_reference` , `registration_ip_address`,
`account_created` , `account_suspended` , `account_closed` , `referral_source`
, `friends_of_ministry_package` , `security_question_1` , `security_answer_1` ,
`security_question_2` , `security_answer_2` , `security_question_3` ,
`security_answer_3` ) VALUES ( NULL , ':f1', ':l1', '', '', '', '', '', ':e1',
'', ':birth_month', ':birth_day', ':validate_password', ':validated',
':last_login', ':last_activity', ':birthday_records', ':greeting_reference',
':registration_ip_address', ':account_created', ':account_suspended',
':account_closed', ':referral_source', ':friends_of_ministry_package',
':security_question_1', '', ':security_question_2', '', ':security_question_3',
'' ) ON DUPLICATE KEY UPDATE `validated` = ':validated', `pass` =
':validate_password', `account_suspended` = ':account_suspended',
`account_closed` = ':account_closed', `last_activity` = ':last_activity',
`registration_ip_address` = ':registration_ip_address';);
$stmt-bindParam(':f1', $f1);
$stmt-bindParam(':l1', $l1);
$stmt-bindParam(':e1', $e1);
$stmt-bindParam(':birth_month', 0);
$stmt-bindParam(':birth_day', 0);
$stmt-bindParam(':validate_password', $validate_password);
$stmt-bindParam(':validated', 5);
$stmt-bindParam(':last_login', $todays_date);
$stmt-bindParam(':last_activity', $todays_date);
$stmt-bindParam(':birthday_records', 15);
$stmt-bindParam(':security_question_1', 0);
$stmt-bindParam(':greeting_reference', 0);
$stmt-bindParam(':registration_ip_address', $registration_ip_address);
$stmt-bindParam(':account_created', $todays_date);
$stmt-bindParam(':account_suspended', -00-00);
$stmt-bindParam(':account_closed', -00-00);
$stmt-bindParam(':referral_source', 2);
$stmt-bindParam(':friends_of_ministry_package', 0);
$stmt-bindParam(':security_question_1', $security_question_1);
$stmt-bindParam(':security_question_2', $security_question_2);
$stmt-bindParam(':security_question_3', $security_question_3);
// insert one row
$stmt-execute();
$stmt-close();
The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info
[PHP-DB] Re: SUM() Math in mySQL
Thanks Chris I tweaked the query and database a bit so I only do 1 query and use PHP with the search results twice: - initially to calculate the number of records which are in the RSS Feed already - if that number is less than 15 I make the new rss_feed.xml file using the same search results because the SELECT queried for the information I am using for this RSS Feed. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info From: Ron Piggott Sent: Sunday, December 19, 2010 11:38 PM To: php-db@lists.php.net Subject: SUM() Math in mySQL I am working on the query below. It’s purpose is to manage the RSS Feed for the site. The part of the query I am struggling with is: SUM(`include_in_rss_feed`) AS current_rss_feed What I need is the total value of *ALL* the `include_in_rss_feed` , not the specific row. `include_in_rss_feed` is an integer. It only ever has a value of 1 My thinking is if the total value of `include_in_rss_feed` and if it is less than 15 the RSS Feed needs to be regenerated because of recent updates to the site. Thanks in advance for helping me. This is for a clients site, not mine. I really appreciate it. Ron SELECT new_rss_feed_listings.reference, new_rss_feed_listings.last_update, SUM(`include_in_rss_feed`) AS current_rss_feed, `ministry_profiles`.`organization`, `ministry_profiles`.`city`, `ministry_profiles`.`province_state`, `ministry_profiles`.`postal_zip_code`, `ministry_profiles`.`country` FROM ( ( SELECT `reference` , `last_update` FROM `ministry_profiles` WHERE `live` =1 ORDER BY `last_update` DESC LIMIT 15 ) UNION ALL ( SELECT `ministry_profiles_reference` , `last_update` FROM `ministry_profiles_activity` WHERE `live` =1 GROUP BY `ministry_profiles_reference` ORDER BY `last_update` DESC LIMIT 15 ) UNION ALL ( SELECT `ministry_profile_reference` , `last_update` FROM `ministry_profiles_listing_details` WHERE `live` =1 GROUP BY `ministry_profile_reference` ORDER BY `last_update` DESC LIMIT 15 ) UNION ALL ( SELECT `ministry_profiles_reference` , `last_update` FROM `ministry_profile_categories` WHERE `live` =1 GROUP BY `ministry_profiles_reference` ORDER BY `last_update` DESC LIMIT 15 ) ) AS new_rss_feed_listings LEFT OUTER JOIN `rss_feed_listings` ON new_rss_feed_listings.reference = `rss_feed_listings`.`ministry_profiles_reference` INNER JOIN `ministry_profiles` ON `ministry_profiles`.`reference` = new_rss_feed_listings.reference ORDER BY new_rss_feed_listings.last_update DESC LIMIT 15 The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] SUM() Math in mySQL
I am working on the query below. It’s purpose is to manage the RSS Feed for the site. The part of the query I am struggling with is: SUM(`include_in_rss_feed`) AS current_rss_feed What I need is the total value of *ALL* the `include_in_rss_feed` , not the specific row. `include_in_rss_feed` is an integer. It only ever has a value of 1 My thinking is if the total value of `include_in_rss_feed` and if it is less than 15 the RSS Feed needs to be regenerated because of recent updates to the site. Thanks in advance for helping me. This is for a clients site, not mine. I really appreciate it. Ron SELECT new_rss_feed_listings.reference, new_rss_feed_listings.last_update, SUM(`include_in_rss_feed`) AS current_rss_feed, `ministry_profiles`.`organization`, `ministry_profiles`.`city`, `ministry_profiles`.`province_state`, `ministry_profiles`.`postal_zip_code`, `ministry_profiles`.`country` FROM ( ( SELECT `reference` , `last_update` FROM `ministry_profiles` WHERE `live` =1 ORDER BY `last_update` DESC LIMIT 15 ) UNION ALL ( SELECT `ministry_profiles_reference` , `last_update` FROM `ministry_profiles_activity` WHERE `live` =1 GROUP BY `ministry_profiles_reference` ORDER BY `last_update` DESC LIMIT 15 ) UNION ALL ( SELECT `ministry_profile_reference` , `last_update` FROM `ministry_profiles_listing_details` WHERE `live` =1 GROUP BY `ministry_profile_reference` ORDER BY `last_update` DESC LIMIT 15 ) UNION ALL ( SELECT `ministry_profiles_reference` , `last_update` FROM `ministry_profile_categories` WHERE `live` =1 GROUP BY `ministry_profiles_reference` ORDER BY `last_update` DESC LIMIT 15 ) ) AS new_rss_feed_listings LEFT OUTER JOIN `rss_feed_listings` ON new_rss_feed_listings.reference = `rss_feed_listings`.`ministry_profiles_reference` INNER JOIN `ministry_profiles` ON `ministry_profiles`.`reference` = new_rss_feed_listings.reference ORDER BY new_rss_feed_listings.last_update DESC LIMIT 15 The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] NULL to 0 result
What change is needed to this query so if “currently_in_rss” is NULL it will be assigned a value of 0 SELECT `reference`, COUNT(`reference`) AS currently_in_rss FROM `ministry_profiles` WHERE `rss_feed_include` = 1 GROUP BY `rss_feed_include` Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] NULL values
When I do the following query in mySQL only 1 record is retrieved. SELECT * FROM `paypal_payment_info` WHERE `os1` NOT LIKE 'commission_paid' I am surprised by this. This one record has no characters in it, but the “INSERT INTO” that created it used: ( `os1` ) VALUES ( ‘’ ) instead of: ( `os1` ) VALUES ( NULL ) . There are a number of records where `os1` is NULL. I would like these rows to retrieve as well. How do I make a WHERE clause for a cell that is NULL ? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] ON DUPLICATE KEY UPDATE
My question is with the syntax below: If the “ON DUPLICATE KEY UPDATE `name` = '$name'” occurs will $_SESSION['referral_clients_reference'] be populated with the mysql_insert_id() value? If not, should I just query for it? Ron mysql_query(INSERT INTO `referral_clients` ( `reference` , `name` , `paypal_email` , `last_update` ) VALUES ( NULL , '$name', '$paypal_email', CURRENT_TIMESTAMP ) ON DUPLICATE KEY UPDATE `name` = '$name';); $_SESSION['referral_clients_reference'] = mysql_insert_id(); The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] French and Spanish Accent Letters
I have a column that is VARCHAR 250. I need it to be able to accept french and spanish accents. The purpose of the column is organization names. The “Collation” default is “latin1_swedish_ci” What do I need to do? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
Re: [PHP-DB] Creating an INDEX on multiple tables?
I have moved the GROUP BY outside now Chris.
In the queries that make up the UNION ALL (such as below) is there a way to
only SELECT matches that are 70% or greater based on the users input? Right
now each and every listing is a search result.
Ron
===
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`
FROM
(
(
( `ministry_profiles` LEFT OUTER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
LEFT OUTER JOIN `ministry_profiles_activity` ON
`ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_profile_categories` ON
`ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
MATCH( `ministry_profiles_activity`.`activity` )
AGAINST
('$search')
AND `ministry_profiles_activity`.`live` =1
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Creating an INDEX on multiple tables?
Is it possible to create one index on multiple tables? I am trying to create a
search function for my web site. The data the user needs to be able to search
is stored in multiple tables. I would like to be able to use MATCH /
AGAINST, like the query below I found online.
SELECT firstname, lastname,comments FROM users WHERE
MATCH(firstname,lastname,comments) AGAINST ('$searchterm')
Ron
Re: [PHP-DB] Creating an INDEX on multiple tables?
...@gmail.com
Sent: Thursday, October 21, 2010 7:12 AM
To: Artur Ejsmont ejsmont.ar...@gmail.com
Cc: Ron Piggott ron.pigg...@actsministries.org; php-db@lists.php.net
Subject: Re: [PHP-DB] Creating an INDEX on multiple tables?
Alternatively, you could try setting that index on each table and use a
UNION to join multiple queries together provided each query returns the
identical data set structures ( or the column types have to match)
Bastien Koert
905-904-0334
Sent from my iPhone
On 2010-10-21, at 7:06 AM, Artur Ejsmont ejsmont.ar...@gmail.com wrote:
I dont think you can create such index across tables.
If you are interested read up on sphinx. Im pretty sure you would be
able to create what you need.
Alternatively ... a super simplistic solution . create one extra
search table with copy of the data and create index there? ;P hehehe
+ would let you do what you need
- would require a lot more IO to support the extra writes (to keep copy
in sync)
It would be cool if a fulltext index could be created on a view :)
Art
On 21 October 2010 09:43, Ron Piggott ron.pigg...@actsministries.org
wrote:
Is it possible to create one index on multiple tables? I am trying to
create a search function for my web site. The data the user needs to be
able to search is stored in multiple tables. I would like to be able to
use MATCH / AGAINST, like the query below I found online.
SELECT firstname, lastname,comments FROM users WHERE
MATCH(firstname,lastname,comments) AGAINST ('$searchterm')
Ron
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating an INDEX on multiple tables?
I am still struggling with this query still although I have made great
progress. The following query (below) executes successfully.
Right now the query returns no rows. I believe this is because there isn't
automatically a record in the following three tables.
`ministry_profiles_activity`
`ministry_profile_categories`
`ministry_profiles_listing_details`
The bare minimum for a listing is only a record in he table
`ministry_profiles`
Is there a way to modify this query to accommodate only a record in the
table `ministry_profiles`
Ron?
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`
FROM
(
(
( `ministry_profiles` INNER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
INNER JOIN `ministry_profiles_activity` ON `ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
INNER JOIN `ministry_profile_categories` ON `ministry_profiles`.`reference`
= `ministry_profile_categories`.`ministry_profiles_reference` )
INNER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
MATCH( `ministry_profiles`.`organization`,
`ministry_profiles`.`address_line_1`, `ministry_profiles`.`address_line_2`,
`ministry_profiles`.`city`, `ministry_profiles`.`province_state`,
`ministry_profiles`.`postal_zip_code`, `ministry_profiles`.`country`,
`ministry_profiles`.`telephone`, `ministry_profiles`.`toll_free`,
`ministry_profiles`.`fax`, `ministry_profiles`.`email`,
`ministry_profiles`.`website` )
AGAINST
('$search')
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating an INDEX on multiple tables?
Very much so. Thank you Bastien. I have never used an OUTER join before
and didn't know it would be required for this project. Ron
--
From: Bastien phps...@gmail.com
Sent: Thursday, October 21, 2010 8:03 PM
To: Ron Piggott ron.pigg...@actsministries.org
Cc: Artur Ejsmont ejsmont.ar...@gmail.com; php-db@lists.php.net
Subject: Re: [PHP-DB] Creating an INDEX on multiple tables?
On 2010-10-21, at 7:38 PM, Ron Piggott ron.pigg...@actsministries.org
wrote:
I am still struggling with this query still although I have made great
progress. The following query (below) executes successfully.
Right now the query returns no rows. I believe this is because there
isn't automatically a record in the following three tables.
`ministry_profiles_activity`
`ministry_profile_categories`
`ministry_profiles_listing_details`
The bare minimum for a listing is only a record in he table
`ministry_profiles`
Is there a way to modify this query to accommodate only a record in the
table `ministry_profiles`
Ron?
SELECT `ministry_profiles`.`reference`,
`ministry_profiles`.`organization` FROM
(
(
( `ministry_profiles` INNER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
INNER JOIN `ministry_profiles_activity` ON
`ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
INNER JOIN `ministry_profile_categories` ON
`ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` )
INNER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
MATCH( `ministry_profiles`.`organization`,
`ministry_profiles`.`address_line_1`,
`ministry_profiles`.`address_line_2`, `ministry_profiles`.`city`,
`ministry_profiles`.`province_state`,
`ministry_profiles`.`postal_zip_code`, `ministry_profiles`.`country`,
`ministry_profiles`.`telephone`, `ministry_profiles`.`toll_free`,
`ministry_profiles`.`fax`, `ministry_profiles`.`email`,
`ministry_profiles`.`website` )
AGAINST
('$search')
Ron,
Try using a left outer join from the table that has records. The inner
join won't return any rows if one record in one table is null. A left
outer join will take all records from the left (first) table regardless of
data being null in the other rows.
Does that make sense?
Bastien=
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating an INDEX on multiple tables?
Bastien (and others) I am still having one problem with this query I don't
know how to resolve:
When I add the category column: `ministry_categories`.`category` as part of
the MATCH () I receive the error:
Incorrect arguments to MATCH
I create the LEFT OUTER JOINs to allow additional columns to be part of the
MATCH. I have made this category column a FULLTEXT index in the
ministry_categories table. The query that produces the error is below.
Is there a way this will work?
Ron
===
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`
FROM
(
(
( `ministry_profiles` LEFT OUTER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
LEFT OUTER JOIN `ministry_profiles_activity` ON
`ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_profile_categories` ON
`ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
MATCH( `ministry_profiles`.`organization`,
`ministry_profiles`.`address_line_1`, `ministry_profiles`.`address_line_2`,
`ministry_profiles`.`city`, `ministry_profiles`.`province_state`,
`ministry_profiles`.`postal_zip_code`, `ministry_profiles`.`country`,
`ministry_profiles`.`telephone`, `ministry_profiles`.`toll_free`,
`ministry_profiles`.`fax`, `ministry_profiles`.`email`,
`ministry_profiles`.`website`, `ministry_categories`.`category` )
AGAINST
('$search')
AND
`ministry_profiles`.`live` =1
GROUP BY `ministry_profiles`.`reference`
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating an INDEX on multiple tables?
The query works using UNION ALL and MATCH ( ) AGAINST --- one table at a
time for the MATCH. The down side is that each SELECT is giving it's own
search results. The directory listings are being displayed multiple times
when one of the SELECTS produces it as a result. How do I limit the
results? Can I do this some how with results.reference and
results.organization (I made the UNION ALLs sub queries)
Thanks for the help.
Ron
SELECT reference, organization FROM (
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`
FROM
(
(
( `ministry_profiles` LEFT OUTER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
LEFT OUTER JOIN `ministry_profiles_activity` ON
`ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_profile_categories` ON
`ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
MATCH( `ministry_profiles`.`organization`,
`ministry_profiles`.`address_line_1`, `ministry_profiles`.`address_line_2`,
`ministry_profiles`.`city`, `ministry_profiles`.`province_state`,
`ministry_profiles`.`postal_zip_code`, `ministry_profiles`.`country`,
`ministry_profiles`.`telephone`, `ministry_profiles`.`toll_free`,
`ministry_profiles`.`fax`, `ministry_profiles`.`email`,
`ministry_profiles`.`website` )
AGAINST
('$search')
AND
`ministry_profiles`.`live` =1
GROUP BY `ministry_profiles`.`reference`
UNION ALL
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`
FROM
(
(
( `ministry_profiles` LEFT OUTER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
LEFT OUTER JOIN `ministry_profiles_activity` ON
`ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_profile_categories` ON
`ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
MATCH( `ministry_categories`.`category` )
AGAINST
('$search')
GROUP BY `ministry_profiles`.`reference`
UNION ALL
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`
FROM
(
(
( `ministry_profiles` LEFT OUTER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
LEFT OUTER JOIN `ministry_profiles_activity` ON
`ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_profile_categories` ON
`ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
MATCH( `ministry_profiles_activity`.`activity` )
AGAINST
('$search')
AND `ministry_profiles_activity`.`live` =1
GROUP BY `ministry_profiles`.`reference`
UNION ALL
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`
FROM
(
(
( `ministry_profiles` LEFT OUTER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
LEFT OUTER JOIN `ministry_profiles_activity` ON
`ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_profile_categories` ON
`ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
MATCH( `ministry_profiles_listing_details`.`contact`,
`ministry_profiles_listing_details`.`year_founded`,
`ministry_profiles_listing_details`.`volunteer_opportunities`,
`ministry_profiles_listing_details`.`employment_opportunities`,
`ministry_profiles_listing_details`.`members_of`,
`ministry_profiles_listing_details`.`major_events`,
`ministry_profiles_listing_details`.`associate_member_of`,
`ministry_profiles_listing_details`.`registration_number`,
`ministry_profiles_listing_details`.`fund_raising` )
AGAINST
('$search')
GROUP BY `ministry_profiles`.`reference`
) AS results
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query for duplicate records
Is there a query you could help me write a SELECT query that would search table `ministry_profiles` for where column `organization` has the same organization more than once? I am trying to delete the duplicate organization records, but I am working with 1,000+ businesses and I can't go through each record looking for duplicates.
[PHP-DB] Escaping an '
For a query similar to: SELECT * FROM `tablle` WHERE `column` LIKE 'Sally's Hair Parlor' how do I escape the ' for 's?
[PHP-DB] Similar to searches
I am wondering if there is a way to or how you would have mySQL query for similar words in comparison to what the user provided. I have a table which contains only single words. It has two columns: - `reference` int(10) NOT NULL AUTO_INCREMENT, - `word` varchar(100) I am wondering if there is a way to cross reference the word the user has provided to similar words in the database (So I could offer SEE ALSO: links). An example: If the word the user has provided is: Forced I would like the mySQL query results to find the words: Forcing, Force and Forces to be the results of the query in the words database. Is there any way of achieving this? Ron The Verse of the Day Encouragement from God's Word www.TheVerseOfTheDay.info -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: SOUNDS_LIKE, SOUNDEX
After looking at the response I tried to make 2 mySQL queries that I need
help with. (I didn't use the PHP functions because I am trying to query
the database for results:
#1:
SELECT `word` FROM `bible_concordance_words` WHERE SOUNDEX('command')
ORDER BY `word` ASC
- There are no results, I don't understand because Commanded, Commander,
Commanding, Commandment, Commandments are all in the datase
#2:
SELECT `word` FROM `bible_concordance_words` WHERE command SOUNDS_LIKE
`word` ORDER BY `word` ASC
- I receive the error:
You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'SOUNDS_LIKE
`word` ORDER BY `word` ASC
I think it wants me to specify an actual word, not the `word` column.
Is there a way to tweak either of the syntaxes I have started?
Ron
Hello, Ron:
I apologize if I was supposed to reply to the list directly, however.. I
saw no direct way to do so that would also ensure my reply to you.
I believe the functions you are looking for would be :
- levenshtein() http://www.php.net/manual/en/function.levenshtein.php
-
Calculate Levenshtein distance between two strings
- metaphone() http://www.php.net/manual/en/function.metaphone.php -
Calculate the metaphone key of a string
- similar_text()
http://www.php.net/manual/en/function.similar-text.php -
Calculate the similarity between two strings
- soundex() http://www.php.net/manual/en/function.soundex.php -
Calculate the soundex key of a string
You would need to use your word and then run the words you want displayed
as
possible matches through this function. Alternatively, SQL also has the
Soundex function , and MySQL has sound_like, which means you could get the
possible matches returned through the dataset.
Hope this helps!
-- Forwarded message --
From: Ron Piggott ron.pigg...@actsministries.org
To: php-db@lists.php.net
Date: Mon, 11 Oct 2010 17:12:17 -0400
Subject: Similar to searches
I am wondering if there is a way to or how you would have mySQL query
for
similar words in comparison to what the user provided.
I have a table which contains only single words. It has two columns:
- `reference` int(10) NOT NULL AUTO_INCREMENT,
- `word` varchar(100)
I am wondering if there is a way to cross reference the word the user
has
provided to similar words in the database (So I could offer SEE ALSO:
links).
An example: If the word the user has provided is: Forced
I would like the mySQL query results to find the words: Forcing, Force
and
Forces to be the results of the query in the words database.
Is there any way of achieving this?
Ron
The Verse of the Day
Encouragement from God's Word
www.TheVerseOfTheDay.info
--
Act only according to that maxim whereby you can at the same time will
that
it should become a universal law. - Kant
He who learns but does not think, is lost; He who thinks but does not
learn
is in great danger. - Confucius
The Verse of the Day
Encouragement from God's Word
www.TheVerseOfTheDay.info
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SELECT / ORDER BY
I wrote the query below to determine the 10 most popular words used: SELECT COUNT( `bible_concordance_usage`.`reference` ) AS word_usage, `bible_concordance_words`.`reference` , `bible_concordance_words`.`word` FROM `bible_concordance_usage` INNER JOIN `bible_concordance_words` ON `bible_concordance_usage`.`bible_concordance_words_reference` = `bible_concordance_words`.`reference` GROUP BY `bible_concordance_usage`.`bible_concordance_words_reference` ORDER BY word_usage DESC, `bible_concordance_words`.`word` ASC, `bible_concordance_usage`.`date_accessed` DESC LIMIT 10 What I don't like about the results is that if 8 words have been used 5 times then the remaining 2 words the query chooses are from words used 4 times. The results are in alphabetical order A to Z for the words used 5 times and back to A to Z for words used 4 times. My question: is there a way to make my query above into a sub query and have a main query order the results of the sub query ORDER BY words ASC so all the words displayed are in alphabetical order? Ron Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] auto_increment
I am wondering if something like the following will work in mySQL: ALTER TABLE `stats` ADD `visits` INT( 25 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ORDER BY `initial_access` ASC This particular syntax won't work though. initial_access is a column that contains a unix timestamp. I am trying to get the auto_increment value to be added in order of sequence of when the visits occurred. Thank you. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] auto_increment
I am receiving the following error Adriano: SQL query: ALTER TABLE `stats` DROP `visits` CREATE TABLE `stats2` LIKE `stats` ; MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE `stats2` LIKE `stats`' at line 2 The complete commands were: ALTER TABLE `stats` DROP `visits` CREATE TABLE `stats2` LIKE `stats`; ALTER TABLE `stats2` ADD COLUMN `visits` INT( 25 ) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(`visits`) FIRST; INSERT INTO `stats2` SELECT *, 0 FROM `stats` ORDER BY `initial_access`; I don't understand the error, your way of creating a table is new to me. Did something small get missed? Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] AUTO_INCREMENT value
I figured it out, the permissions weren't set on the user to allow the command to work. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] CURDATE
I am wondering why: SELECT * FROM `bible_concordance_usage` WHERE `date_accessed` = CURDATE() Doesn't work when `date_accessed` is column type timestamp And What would work? Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] COUNT() query help
I am making a Bible concordance. I need help with the query that figures out how many times each word is in the Bible. The COUNT() in the query below is giving me the total number of words for the letter of the alphabet, not the specific word, and is causing there to be only 1 search result. I have 2 tables for the concordance. I have a words table and I have a table that records the words each verse of the Bible contains. The common field between both tables is: `bible_concordance_words`.`reference` equals: `bible_concordance_word_reference`.`bible_concordance_words_reference` This is the query so far. What change do I need to make so COUNT() will tell me the number of times a word is used in the Bible with the design of my tables? SELECT `bible_concordance_words`.`reference`, `bible_concordance_words`.`word`, COUNT(`bible_concordance_word_reference`.`bible_concordance_words_reference`) AS occurrences FROM `bible_concordance_words` INNER JOIN `bible_concordance_word_reference` ON `bible_concordance_words`.`reference` = `bible_concordance_word_reference`.`bible_concordance_words_reference` WHERE `word` LIKE '$letter%' ORDER BY `word` ASC Thanks for your help. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SELECT with ' in search term
If the variable $segment has an ' in it the $query won't work because of having 3 ' 's. Should I be using: $segment = mysql_real_escape_string($segment); before querying the database? $query=SELECT `reference` FROM `bible_concordance_words` WHERE `word` = '$segment' LIMIT 1; Please note: $segment wasn't submitted through a form. Thanks. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] NULL to 0
Thanks, Ron -- -Original Message- From: Andrés G. Montañez andresmonta...@gmail.com To: ron.pigg...@actsministries.org Cc: php-db@lists.php.net Subject: Re: [PHP-DB] NULL to 0 Date: Sat, 17 Jul 2010 23:20:20 -0300 SELECT IFNULL(SUM(`my_Bible_trivia_knowledge_questions_answered`.`score`), 0) AS total_score, IFNULL(`my_Bible_trivia_knowledge_profile`.`questions_answered`, 0) AS questions_answered In these cases you must use the IFNULL function, for testing the value. -- Andrés G. Montañez Zend Certified Engineer Montevideo - Uruguay
[PHP-DB] LEFT JOIN query help
I am writing a Bible trivia application. I am trying to write the mySQL query that will select the next question reference number and the current question is answered. The value I want to retrieve It is in the field: `verse_of_the_day_Bible_trivia`.`reference` I don't think I have my LEFT JOIN's right. When I take away the WHERE clause only the records the user has answered are selected. Then they are being eliminated with the WHERE clause. I am hoping the results join the 3 tables together --- really wide --- with the user profile on the left hand side and then the question is the middle and if the user has answered it then this record on the right hand side, otherwise the fields are NULL. Does this make sense? Ron SELECT `verse_of_the_day_Bible_trivia`.`reference` FROM ( `my_Bible_trivia_knowledge_profile` LEFT JOIN `my_Bible_trivia_knowledge_questions_answered` ON `my_Bible_trivia_knowledge_profile`.`reference` = `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_trivia_knowledge_profile_reference` ) LEFT JOIN `verse_of_the_day_Bible_trivia` ON `verse_of_the_day_Bible_trivia`.`reference` = `my_Bible_trivia_knowledge_questions_answered`.`verse_of_the_day_Bible_trivia_reference` WHERE `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_trivia_knowledge_profile_reference` $user_reference AND `my_Bible_trivia_knowledge_questions_answered`.`score` IS NULL AND `verse_of_the_day_Bible_trivia`.`live` =1 ORDER BY RAND() LIMIT 1 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: LEFT JOIN query help
I am still working on this query and wondering if I should be taking a different approach --- to use a sub query to figure out which questions have been answered and then an outter query to not select one of them. But mySQL is giving me the error that the subquery has more than 1 row --- I have answered 2 questions. Would someone be able to clean up this query / sub query combination? SELECT `verse_of_the_day_Bible_trivia`.`reference` FROM `verse_of_the_day_Bible_trivia` WHERE `verse_of_the_day_Bible_trivia`.`reference` NOT LIKE ( SELECT `verse_of_the_day_Bible_trivia`.`reference` FROM `verse_of_the_day_Bible_trivia` LEFT JOIN `my_Bible_trivia_knowledge_questions_answered` ON `my_Bible_trivia_knowledge_questions_answered`.`verse_of_the_day_Bible_trivia_reference` = `verse_of_the_day_Bible_trivia`.`reference` LEFT JOIN `my_Bible_trivia_knowledge_profile` ON `my_Bible_trivia_knowledge_profile`.`reference` = `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_trivia_knowledge_profile_reference` WHERE `verse_of_the_day_Bible_trivia`.`live` =1 AND `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_trivia_knowledge_profile_reference` = $user_reference ) AND `verse_of_the_day_Bible_trivia`.`live` =1 ORDER BY RAND() LIMIT 1 I am writing a Bible trivia application. I am trying to write the mySQL query that will select the next question reference number and the current question is answered. The value I want to retrieve It is in the field: `verse_of_the_day_Bible_trivia`.`reference` I don't think I have my LEFT JOIN's right. When I take away the WHERE clause only the records the user has answered are selected. Then they are being eliminated with the WHERE clause. I am hoping the results join the 3 tables together --- really wide --- with the user profile on the left hand side and then the question is the middle and if the user has answered it then this record on the right hand side, otherwise the fields are NULL. Does this make sense? Ron SELECT `verse_of_the_day_Bible_trivia`.`reference` FROM ( `my_Bible_trivia_knowledge_profile` LEFT JOIN `my_Bible_trivia_knowledge_questions_answered` ON `my_Bible_trivia_knowledge_profile`.`reference` = `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_trivia_knowledge_profile_reference` ) LEFT JOIN `verse_of_the_day_Bible_trivia` ON `verse_of_the_day_Bible_trivia`.`reference` = `my_Bible_trivia_knowledge_questions_answered`.`verse_of_the_day_Bible_trivia_reference` WHERE `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_trivia_knowledge_profile_reference` $user_reference AND `my_Bible_trivia_knowledge_questions_answered`.`score` IS NULL AND `verse_of_the_day_Bible_trivia`.`live` =1 ORDER BY RAND() LIMIT 1 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: LEFT JOIN query help
Thanks. That answer worked. Ron -- -Original Message- From: Kesavan Rengarajan k...@trk7.com To: ron.pigg...@actsministries.org ron.pigg...@actsministries.org Cc: ron.pigg...@actsministries.org ron.pigg...@actsministries.org, php-db@lists.php.net php-db@lists.php.net Subject: Re: [PHP-DB] Re: LEFT JOIN query help Date: Mon, 19 Jul 2010 08:21:00 +1000 Change 'NOT LIKE' to 'NOT IN' in the outer query. Sent from my iPhone On 19/07/2010, at 4:15 AM, Ron Piggott ron.pigg...@actsministries.org wrote: I am still working on this query and wondering if I should be taking a different approach --- to use a sub query to figure out which questions have been answered and then an outter query to not select one of them. But mySQL is giving me the error that the subquery has more than 1 row --- I have answered 2 questions. Would someone be able to clean up this query / sub query combination? SELECT `verse_of_the_day_Bible_trivia`.`reference` FROM `verse_of_the_day_Bible_trivia` WHERE `verse_of_the_day_Bible_trivia`.`reference` NOT LIKE ( SELECT `verse_of_the_day_Bible_trivia`.`reference` FROM `verse_of_the_day_Bible_trivia` LEFT JOIN `my_Bible_trivia_knowledge_questions_answered` ON `my_Bible_trivia_knowledge_questions_answered`.`verse_of_the_day_Bible_trivia_reference` = `verse_of_the_day_Bible_trivia`.`reference` LEFT JOIN `my_Bible_trivia_knowledge_profile` ON `my_Bible_trivia_knowledge_profile`.`reference` = `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_trivia_knowledge_profile_reference` WHERE `verse_of_the_day_Bible_trivia`.`live` =1 AND `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_trivia_knowledge_profile_reference` = $user_reference ) AND `verse_of_the_day_Bible_trivia`.`live` =1 ORDER BY RAND() LIMIT 1 I am writing a Bible trivia application. I am trying to write the mySQL query that will select the next question reference number and the current question is answered. The value I want to retrieve It is in the field: `verse_of_the_day_Bible_trivia`.`reference` I don't think I have my LEFT JOIN's right. When I take away the WHERE clause only the records the user has answered are selected. Then they are being eliminated with the WHERE clause. I am hoping the results join the 3 tables together --- really wide --- with the user profile on the left hand side and then the question is the middle and if the user has answered it then this record on the right hand side, otherwise the fields are NULL. Does this make sense? Ron SELECT `verse_of_the_day_Bible_trivia`.`reference` FROM ( `my_Bible_trivia_knowledge_profile` LEFT JOIN `my_Bible_trivia_knowledge_questions_answered` ON `my_Bible_trivia_knowledge_profile`.`reference` = `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_trivia_knowledge_profile_reference` ) LEFT JOIN `verse_of_the_day_Bible_trivia` ON `verse_of_the_day_Bible_trivia`.`reference` = `my_Bible_trivia_knowledge_questions_answered`.`verse_of_the_day_Bible_trivia_reference` WHERE `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_trivia_knowledge_profile_reference` $user_reference AND `my_Bible_trivia_knowledge_questions_answered`.`score` IS NULL AND `verse_of_the_day_Bible_trivia`.`live` =1 ORDER BY RAND() LIMIT 1 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] NULL to 0
What modification does this query need that if either total_score or questions_answered have a value of NULL that it will be changed to 0 (ZERO) Ron SELECT SUM(`my_Bible_trivia_knowledge_questions_answered`.`score`) AS total_score, `my_Bible_trivia_knowledge_profile`.`questions_answered` FROM `my_Bible_trivia_knowledge_questions_answered` INNER JOIN `my_Bible_trivia_knowledge_profile` ON `my_Bible_trivia_knowledge_questions_answered`.`my_Bible_trivia_knowledge_profile_reference` = `my_Bible_trivia_knowledge_profile`.`reference` WHERE `my_Bible_trivia_knowledge_profile`.`ip_address` = '$ip_address' -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Broken query
I am trying to write a query to select a trivia question, but I don't want the trivia question category to be the same two days in a row so I added a second SELECT syntax to find out what category was used yesterday. This works when I test it live, but doesn't work when it is part of a cron job. How do I get the value of `Bible_trivia_category_reference` from the second SELECT query to be used in the first? What change is needed? Ron SELECT * FROM `verse_of_the_day_Bible_trivia` WHERE `assigned_date` = '-00-00' AND `seasonal_use` = $bible_trivia_application AND `live` =1 AND NOT `Bible_trivia_category_reference` = ( SELECT `Bible_trivia_category_reference` FROM `verse_of_the_day_Bible_trivia` WHERE `assigned_date` = '$last_mailing_date' LIMIT 1 ) ORDER BY RAND() LIMIT 1 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Broken query
Yes I have checked that $last_mailing_date contains a date. I was wrong, It doesn't work when it is live. I had only ran the first query before I added the AND NOT portion, sorry. Ron I am trying to write a query to select a trivia question, but I don't want the trivia question category to be the same two days in a row so I added a second SELECT syntax to find out what category was used yesterday. This works when I test it live, but doesn't work when it is part of a cron job. How do I get the value of `Bible_trivia_category_reference` from the second SELECT query to be used in the first? What change is needed? Ron SELECT * FROM `verse_of_the_day_Bible_trivia` WHERE `assigned_date` = '-00-00' AND `seasonal_use` = $bible_trivia_application AND `live` =1 AND NOT `Bible_trivia_category_reference` = ( SELECT `Bible_trivia_category_reference` FROM `verse_of_the_day_Bible_trivia` WHERE `assigned_date` = '$last_mailing_date' LIMIT 1 ) ORDER BY RAND() LIMIT 1 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Another UNION ALL query
I have a 'Highlights' heading on my home page. It is for links to content on the site ... like specific web pages I have designed the query below to select the two most popular and least popular pages used on the site to be the Highlights. (Each time a web page is accessed user_hits is increased by 1.) I would like to display a fifth one that is ORDER BY RAND () LIMIT 1 --- Only I don't know how to ensure it isn't one of the four that are being displayed already. Any suggestions? Ron SELECT `highlights`.`reference`, `highlights`.`page_command`, `highlights`.`page_title` FROM ( ( SELECT `reference`, `page_command`, `page_title` FROM `user_pages` WHERE `include_in_highlights` =1 ORDER BY `user_hits` DESC LIMIT 2 ) UNION ALL ( SELECT `reference`, `page_command`, `page_title` FROM `user_pages` WHERE `include_in_highlights` =1 ORDER BY `user_hits` ASC LIMIT 2 ) ) AS highlights ORDER BY `highlights`.`page_title` ASC -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] UNION ALL query help
I am received the error message: Unknown column 'title' in 'field list' from the query below. I am not understanding what I have done wrong. The goal of the query is with for a RSS feed determining the 15 most current articles. Ron SELECT title, content_date, content, reference FROM ( ( SELECT `page_title`, `last_update`, 'article', `reference` FROM `user_pages` WHERE `include_in_highlights` =1 ORDER BY `last_update` DESC LIMIT 15 ) UNION ALL ( SELECT `blog`.`entry_title`, `blog`.`entry_date`, `blog`.`entry`, `blog`.`reference` FROM blog WHERE blog_owners_reference =1 ORDER BY blog.entry_date DESC, blog.reference DESC LIMIT 15 ) ) AS rss_feed ORDER BY rss_feed.content_date DESC LIMIT 15 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: UNION ALL query help
Ok. I have received help and got it fixed. I didn't have the variables matching the first query. I haven't done a UNION ALL recently. Thanks. Ron
Re: [PHP-DB] mySQL date query
It is in a 'date' column type -MM-DD what is the format of the date u are storing ? Chaitanya On Tue, Apr 13, 2010 at 11:50 AM, Ron Piggott ron.pigg...@actsministries.org wrote: I am trying to write a mySQL query on my stats table. I am trying to determine the number of records (users) during a 7 day period ending yesterday. I always to keep it current ... Yesterday will keep changing. In other words I want to know the number of users who accessed the web site during seven full days. This is the beginning of the query. The date column is date. SELECT count(`visits`) as users FROM `stats` WHERE `date` Thanks, Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Shopping cart application question
I have done something like you have said below Chris.
I put the code which is common to be loops in it's own file and I did an
INCLUDE
Ron
-Original Message-
From: chris smith dmag...@gmail.com
To: ron.pigg...@actsministries.org
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] Shopping cart application question
Date: Sun, 4 Apr 2010 13:36:05 +1000
The } else { confuses PHP. I am not sure what I should be doing.
You're missing at least one brace.
If the code you supplied is a copy of what you have, you're missing a
} for the end of the while loop and one for the end of the else
condition.
So it's treating } else { as part of the while construct (and while()
doesn't have anything like an else condition).
if ( $_SESSION['user_reference'] 0 ) {
snip
$i=0;
while ( $i $cart_records_found ) {
} // end while
} else {
foreach ($_SESSION['order'] AS $key = $value ) {
}
} // end else condition
[PHP-DB] Shopping cart application question
I am writing a shopping cart application. I am expanding the application
to have a store membership component.
Right now I am saving the customers selections in one of two ways:
- In the session variable $_SESSION['order'] if they aren't logged in
- For those who have logged into their account I am saving their
selections in the table membership_shopping_carts
I have ran into trouble trying to output their choices under the cart
contents heading. I want to invoke one of two loops --- A 'while' loop
for the session variable and a mySQL query for the logged in users.
The } else { confuses PHP. I am not sure what I should be doing.
$_SESSION['user_reference'] stores the value of the logged in user. This
is how I tell if s/he is logged in or not --- and which loop I want to
invoke.
Suggestions?
Ron
if ( $_SESSION['user_reference'] 0 ) {
$user_reference = $_SESSION['user_reference'];
$query = SELECT `store_product_profile`.`reference` FROM
`membership_shopping_carts` INNER JOIN `store_product_profile` on
`store_product_profile`.`reference` =
`membership_shopping_carts`.`store_product_profile_reference` WHERE
`membership_shopping_carts`.`member_reference` = $user_reference ORDER BY
`store_product_profile`.`product_name` ASC;
$shopping_cart_content_result=mysql_query($query);
$cart_records_found=mysql_numrows($shopping_cart_content_result);
$i=0;
while ( $i $cart_records_found ) {
} else {
foreach ($_SESSION['order'] AS $key = $value ) {
}
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Rounding up results
Is there a way to round up in mySQL? This result may give 3.2 --- so I want the result to be 4? SELECT ( count(`reference`) / $items_per_page ) AS total_pages FROM `store_product_profile` WHERE `store_category_reference` =$reference AND `live` =1 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] mysql_close()
Does mysql_close() close all open database connections? How would I specify which connection to close? Ron
Re: [PHP-DB] mysql_close()
Guys thanks for your help. When I went back to my code I figured out what threw me off: I only used one database connection to begin with ... I had created a user that exists on the two databases for the part of the application I am writing. I forgot about this. Thanks! Ron -Original Message- From: pretz...@mail.uni-paderborn.de Reply-to: pretz...@mail.uni-paderborn.de To: ron@actsministries.org Subject: Re: [PHP-DB] mysql_close() Date: Sat, 20 Feb 2010 22:48:22 +0100 http://php.net/manual/en/function.mysql-close.php On 20.02.2010 22:27, Ron Piggott wrote: Does mysql_close() close all open database connections? How would I specify which connection to close? Ron
[PHP-DB] Login query
I am wondering what others do for a login query. I think there could be two results: correct e-mail password; correct e-mail wrong password So far my login query is: SELECT * FROM `member` WHERE `email` = '$my_email' AND `pass` LIKE BINARY '$my_password' LIMIT 1 This wouldn't tell me if the user has the wrong password. Is there a better way to do this? Ron
[PHP-DB] IFNULL
How do I add IFNULL to the LEFT JOIN query? I either want the result from the database or a 0 instead of the word NULL Ron SELECT `store_product_profile`.`reference` , inventory.inventory, sold_1.quantity, sold_2.quantity, sold_3.quantity FROM `store_product_profile` LEFT JOIN ( SELECT `store_product_inventory_control`.`store_product_profile_reference` , SUM( `store_product_inventory_control`.`quantity` ) AS inventory FROM `store_product_inventory_control` GROUP BY `store_product_inventory_control`.`store_product_profile_reference` ) AS inventory ON `store_product_profile`.`reference` = `inventory`.`store_product_profile_reference` -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Cross reference query help
The following is a query I am working on today. I need help tweaking on it. What I am trying to get for results from the query are where there are rows in either table, not all 3 tables linked together. In other words I am trying to INNER JOIN these two pairs of tables for possible results: `store_product_profile` and `paypal_cart_info` OR `store_product_profile` and `mail_order_cart` SELECT `store_product_profile`.`product_name` FROM ( `store_product_profile` INNER JOIN `paypal_cart_info` ON `store_product_profile`.`reference` = `paypal_cart_info`.`itemnumber` ) INNER JOIN `mail_order_cart` ON `store_product_profile`.`reference` = `mail_order_cart`.`store_product_profile_reference` WHERE `store_product_profile`.`discontinued` =0 AND `store_product_profile`.`reference` =7 Finally I need help with the GROUP BY syntax also. I only want the name of the product once, not if it was in several previous purchases. Any help is appreciated. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
