Re: [PHP-DB] SQL syntax
SELECT orderid
FROM ORDERS_TABLE
WHERE orderstatus IN ( 'Cancelled', 'New', 'Denied',
'Expired' , 'Failed' , 'Pending' , 'Refunded' , 'Reversed' , 'Under Review'
, 'Voided') AND orderdate '.mysqli_real_escape_string($
yesterday);
Another option would be to use either of these functions
-
Find-in-sethttp://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set.
This is useful if your data type is SET/ENUM type
-
Fieldhttp://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field
On Tue, Jan 15, 2013 at 2:59 PM, Karl DeSaulniers k...@designdrumm.comwrote:
SELECT orderid
FROM ORDERS_TABLE
WHERE orderstatus = 'Cancelled' OR (orderstatus = ('New'
OR 'Denied' OR 'Expired' OR 'Failed' OR 'Pending' OR 'Refunded' OR
'Reversed' OR 'Under Review' OR 'Voided') AND orderdate
'.mysqli_real_escape_string($**yesterday).');
regds
amit
The difference between fiction and reality? Fiction has to make sense.
Re: [PHP-DB] Re: PDO Mysql data truncation missing error
Dear Matt, Even with MySQL, u get the warning as the show warnings is enabled. With php bound scripts, you have to check warnings to see the warnings. However, php is quite flexible and you an use error_reporting(E_WARNING) for non-fatal error reporting regds amit On Aug 21, 2012 10:41 AM, Matt Pelmear mjpelm...@gmail.com wrote: On 08/21/2012 01:08 AM, David Robley wrote: Matt Pelmear wrote: Hello, I'm trying to detect data truncation on insert to MySQL using PDO. As far as I can tell, this gets reported at least in some cases (ex: http://drupal.org/node/1528628**), but I have been unable to see this myself. The test table I'm using has a column that is VARCHAR(5): mysql describe test; +---+--+--**+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--**+-+-+---+ | id| int(10) unsigned | YES | | NULL| | | data | varchar(5) | YES | | NULL| | +---+--+--**+-+-+---+ 2 rows in set (0.00 sec) My test script inserts a ten character string into the 5 character column: PHP TEST CODE $pdo = new PDO( 'mysql:host=localhost;dbname=**test', 'username', 'password' ); $pdo-setAttribute(PDO::ATTR_**ERRMODE, PDO::ERRMODE_EXCEPTION); $retval = $pdo-query( 'INSERT INTO `test` (data) VALUES (1234567890)' ); print_r( $retval ); END TEST CODE This results in a new row in the `test` table, truncated after the 5th character as expected, but the truncation is not reported. (running in php 5.3.2 and 5.3.4) Running the same query directly in the mysql command line shows a warning as expected. Am I missing something simple here? Thanks, Matt Caveat: I don't use PDO but maybe the PDO::ERRMODE_WARNING attribute may do what you want? Cheers Interesting idea. I gave it a try, but got the same result! I think PDO::ERRMODE_WARNING tells PDO to give PHP warnings versus throw exceptions when there are problems (if I'm understanding it properly). -Matt -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Excel to HTML table using PHP
Try this linkhttp://devzone.zend.com/27/reading-and-writing-spreadsheets-with-php/. What i understand is that u do not want the end user to open the excel sheet. However, u have to read the data part and display in HTML using PHP/Javascript regds amit The difference between fiction and reality? Fiction has to make sense. On Wed, Mar 21, 2012 at 5:53 PM, Gu®u nagendra802...@gmail.com wrote: Hi All, I have searched all around the net and unable to find excel to html table using php. What I want is, say I have an excel sheet and I want to export the excel data to HTML table *without* *opening it*. Then I can use the HTML table for later function. Please help me with this. -- *Best, * *Gu®u*
Re: [PHP-DB] Select from multiple tables
Dear Kranthi You have to be clear what you decide especially when you are getting multiple rows. To get just a single row you can use LIMIT clause. But it would return only one row. Now you have to decide which row. So i think you decide on what you require and see how can you uniquely identify that row regds amit The difference between fiction and reality? Fiction has to make sense. On Thu, Mar 1, 2012 at 9:45 AM, Kranthi Krishna kranthi...@gmail.comwrote: Hi, The examples I saw were regarding cartesian join not inner join. I will read about inner joins. Also, the example i mentioned seems to be a mistake. Both school and type will not be similar at the same time Kranthi. http://goo.gl/e6t3 On 1 March 2012 09:26, Kranthi Krishna kranthi...@gmail.com wrote: Hi, Thanks for the input. I have seen some tutorials on joins, they all suggest that MySql returns multiple rows For example -- School | Board 1 -- School | Board 1 - Now if I have another one-to-many relation --- School | Board 1 | Type 1 --- School | Board 1 | Type 2 --- School | Board 2 | Type 1 --- School | Board 2 | Type 2 Using UNIQUE or something similar (like php.net/array_search ) causes problems when Type 1 = Type 2 etc. Kranthi. http://goo.gl/e6t3 On 29 February 2012 19:43, Michael Stowe mikegst...@gmail.com wrote: Select table1.item1, table2.item1 from table1 inner join table2 on table1.key = table2.foreignKey Where... You can also utilize left and right join to get data if there isn't a direct match (ie customer may not have ordered anything so you want to do a left join on orders as there may not be any order data but you still want to get the customer info). Hope that helps, Mike Sent from my iPhone On Feb 29, 2012, at 8:01 AM, Kranthi Krishna kranthi...@gmail.com wrote: Hi all, Say I have an object like array schoolName = string board = array string string I generally create two MySql tables schools: id PRIMARY KEY, SchoolName boards: id FOREGIN KEY refers Table A(id), board and then do two selects. The problem is that, the number of selects increase as the number of one-to-many relationships increase. Is there a better way to do this ? I have to extend an existing code so I cannot use any libraries like doctrine Kranthi. http://goo.gl/e6t3 -- 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] Problem w/query - again
Dear Ethan
It seems you are trying to build a query.But you are not getting field
names. If you required field names then change the following line to
foreach ( $allowed_fields AS $field = $_POST['field'])
to
foreach ( $allowed_fields AS $field)
This would convert the variable field to value. In yoyr line the variable
field is treated as array index
regds
amit
The difference between fiction and reality? Fiction has to make sense.
On Fri, Feb 10, 2012 at 9:40 AM, Ethan Rosenberg eth...@earthlink.netwrote:
Dear list -
This did not seem to post, so I am sending it again.
If it did post, and I missed it, my apologies.
Ethan
Dear list -
I have the following code:
$query = select * from Intake3 where 1;
$allowed_fields = array('Site', 'MedRec', 'Fname', 'Lname',
'Phone', 'Sex', 'Height');
foreach ( $allowed_fields AS $field = $_POST['field'])
{
if ( ! empty( $_POST['field'] ) )
{
$query .= AND '$field' = '$_POST[$field]' ;
echo $query;
}
}
This is the value of $_POST:
Array
(
[Site] = AA
[MedRec] = 1
[Fname] =
[Lname] =
[Phone] =
[Height] =
[welcome_already_seen] = already_seen
)
I receive the following errors on run:
Notice: Undefined offset: 0 in /var/www/srchrhsptl4.php on line 135
select * from Intake3 where 1 AND '0' = ''
Notice: Undefined offset: 1 in /var/www/srchrhsptl4.php on line 135
select * from Intake3 where 1 AND '0' = '' AND '1' = ''
Notice: Undefined offset: 2 in /var/www/srchrhsptl4.php on line 135
select * from Intake3 where 1 AND '0' = '' AND '1' = '' AND '2' = ''
Advice and help please.
Thanks.
Ethan
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Problem w/query - again
Dear Ethan The line you are getting is because the $_POST[fieldname] is blank. So for the following line if ( ! empty( $_POST['field'] ) ) change it to if ( ! empty( $_POST[$field] ) ) Your line : Program is searxchinbg for variable name field New line : The Program is seacging for varable stored in $field. Rember to use double quotes And to verify the value echo $_POST[$field] before your if line i.e. if ( ! empty( $_POST[$field] ) ) regds amit The difference between fiction and reality? Fiction has to make sense. On Fri, Feb 10, 2012 at 11:04 AM, Ethan Rosenberg eth...@earthlink.netwrote: At 12:13 AM 2/10/2012, Amit Tandon wrote: Dear Ethan It seems you are trying to build a query.But you are not getting field names. If you required field names then change the following line to foreach ( $allowed_fields AS $field = $_POST['field']) to foreach ( $allowed_fields AS $field) This would convert the variable field to value. In yoyr line the variable field is treated as array index regds amit The difference between fiction and reality? Fiction has to make sense. snip Advice and help please. Thanks. Ethan PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Amit - Thanks. Tried it. Still does not work. This is the query I get: select * from Intake3 where 1 Ethan
Re: [PHP-DB] Tables and foreign keys
regds amit The difference between fiction and reality? Fiction has to make sense. On Mon, Dec 26, 2011 at 1:55 PM, Karl DeSaulniers k...@designdrumm.comwrote: On Dec 26, 2011, at 1:57 AM, Amit Tandon wrote: Dear Karl Foreign key is a good option (provided you use InnoDB database) to automate the deletion/updation of cart details when you remove cart. Besides 1:1 cardinality is good or bad can only be decided on the semantics of your tables. As a an aside, if it is 1:1 cardinality, then you can probably merge the two tables regds amit The difference between fiction and reality? Fiction has to make sense. On Mon, Dec 26, 2011 at 12:38 PM, Karl DeSaulniers k...@designdrumm.com wrote: Hello all, I have two database tables. Cart and a cartdetails. I want the information for the cartdetails to be based on/autofilled info from cart. I want the ability to delete from cart and cartdetails to automatically clear as well. Would a foreign key be best suited for this? What kind of cardnaility would I use 1:1 ? Hope I make sense. Hope everyone had a great holidays! Best, Karl DeSaulniers Design Drumm http://designdrumm.com Thanks Amit for the reply. Ok, so if you can not merge the two tables, then it is not a 1:1 relationship? EG: (table)CART: cartID - PRIMARY username firstname lastname product long description quantity options price (table)CARTDETAILS: cartdetailsID cartID - Foreign Key (Unique) product short description (taken from long des. in CART) quantity options price Is this a 1:1 if the key is between the two cartID's? No! it is 1:N becuase of Multiple rows in Cartid = 1 row in Cart What is actually happening when I make a foreign key? You link the two tables and try to find matching rows of cart in cart id What does the foreign key cover? Just the row its linked to or the whole table? The foreing key helps you uniquely identift set of rows. Foerign key actully help you identify the row(s). And then column of thos rows give you your value For this structure I would like, in order for their to be cartdetails, there should be a cart. If there is a cart then a cartdetails needs to automatically/progmatically generate. (I think I read somewhere that MySQL is capable of doing this before sending back responses. Please correct me if I am wrong or if its unrelated.) What kind of relation is that? Also, if you or someone could explain what these cardinality rules mean. What.. 0 or 1 does? 1:1 1+ does? 1:M 0,1+does? M:N - you figure correctly. But this is for both the tables i.e 1+ psosibility in both the tables. And this is generally table which is linked to two tables Many (kind-of figured this one out, perhaps not.) etc.. I think that would help me greatly. I hope I am not sounding like a help vamp. I only ask because my google foo has failed me on where to search for these specific answers. Links are appreciated. I like to do stuff on my own but sometimes the brain cramps. :) TIA Best, Karl DeSaulniers Design Drumm http://designdrumm.com
Re: [PHP-DB] Tables and foreign keys
Dear KarlRefer Wiki entryhttp://en.wikipedia.org/wiki/Entity-relationship_model. The last diagram shows one to many relationship in many ways. That may help you in clearing your doubts about the comma which is a way of representing the relationship Quoted from MS Access example One-To-Many Relationships A one-to-many relationship is the most common type of relationship. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A. For example, the Publishers and Titles tables have a one-to-many relationship: each publisher produces many titles, but each title comes from only one publisher. A one-to-many relationship is created if only one of the related columns is a primary key or has a unique constraint. Many-To-Many Relationships In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B. For example, the Authors table and the Titles table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the TitleAuthors table. The primary key of the TitleAuthors table is the combination of the au_id column (the authors table’s primary key) and the title_id column (the Titles table’s primary key). One-To-One Relationships In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints. regds amit The difference between fiction and reality? Fiction has to make sense. On Mon, Dec 26, 2011 at 2:43 PM, Karl DeSaulniers k...@designdrumm.comwrote: On Dec 26, 2011, at 3:05 AM, Amit Tandon wrote: regds amit The difference between fiction and reality? Fiction has to make sense. On Mon, Dec 26, 2011 at 1:55 PM, Karl DeSaulniers k...@designdrumm.com wrote: On Dec 26, 2011, at 1:57 AM, Amit Tandon wrote: Dear Karl Foreign key is a good option (provided you use InnoDB database) to automate the deletion/updation of cart details when you remove cart. Besides 1:1 cardinality is good or bad can only be decided on the semantics of your tables. As a an aside, if it is 1:1 cardinality, then you can probably merge the two tables regds amit The difference between fiction and reality? Fiction has to make sense. On Mon, Dec 26, 2011 at 12:38 PM, Karl DeSaulniers k...@designdrumm.com wrote: Hello all, I have two database tables. Cart and a cartdetails. I want the information for the cartdetails to be based on/autofilled info from cart. I want the ability to delete from cart and cartdetails to automatically clear as well. Would a foreign key be best suited for this? What kind of cardnaility would I use 1:1 ? Hope I make sense. Hope everyone had a great holidays! Best, Karl DeSaulniers Design Drumm http://designdrumm.com Thanks Amit for the reply. Ok, so if you can not merge the two tables, then it is not a 1:1 relationship? EG: (table)CART: cartID - PRIMARY username firstname lastname product long description quantity options price (table)CARTDETAILS: cartdetailsID cartID - Foreign Key (Unique) product short description (taken from long des. in CART) quantity options price Is this a 1:1 if the key is between the two cartID's? No! it is 1:N becuase of Multiple rows in Cartid = 1 row in Cart What is actually happening when I make a foreign key? You link the two tables and try to find matching rows of cart in cart id What does the foreign key cover? Just the row its linked to or the whole table? The foreing key helps you uniquely identift set of rows. Foerign key actully help you identify the row(s). And then column of thos rows give you your value For this structure I would like, in order for their to be cartdetails, there should be a cart. If there is a cart then a cartdetails needs to automatically/progmatically generate. (I think I read somewhere that MySQL is capable of doing this before sending back responses. Please correct me if I am wrong or if its unrelated.) What kind of relation is that? Also, if you or someone could explain what these cardinality rules mean. What.. 0 or 1 does? 1:1 1+ does? 1:M 0,1+does? M:N - you figure correctly. But this is for both the tables i.e 1+ psosibility in both the tables. And this is generally table which is linked to two tables So the 0=first table and ,1+ = subsezuent tables? Is there such thing as 1+,0? Or is that what 1+ is? Sry, the coma throws me off a bit. Many (kind-of figured this one out, perhaps
Re: [PHP-DB] Tables and foreign keys
Dear Karl Foreign key is a good option (provided you use InnoDB database) to automate the deletion/updation of cart details when you remove cart. Besides 1:1 cardinality is good or bad can only be decided on the semantics of your tables. As a an aside, if it is 1:1 cardinality, then you can probably merge the two tables regds amit The difference between fiction and reality? Fiction has to make sense. On Mon, Dec 26, 2011 at 12:38 PM, Karl DeSaulniers k...@designdrumm.comwrote: Hello all, I have two database tables. Cart and a cartdetails. I want the information for the cartdetails to be based on/autofilled info from cart. I want the ability to delete from cart and cartdetails to automatically clear as well. Would a foreign key be best suited for this? What kind of cardnaility would I use 1:1 ? Hope I make sense. Hope everyone had a great holidays! Best, Karl DeSaulniers Design Drumm http://designdrumm.com
Re: [PHP-DB] Need help with updating existing mysql records with something else
Dear Guru
U can update the record with following
===
$result = mysql_query(UPDATE tax set mwool40_
totqty = $res, $connection) or die(error
updating database);
if (mysql_affected_rows($connection) != 1 (/* no of rows that should be
updated */)
{
die(Update problem) ;
}
The only issue is the where condition that you would put in the query
UPDATE tax set mwool40_totqty = $res where ...
regds
amit
The difference between fiction and reality? Fiction has to make sense.
2011/11/11 Guru™ nagendra802...@gmail.com
Hi All,
I want to update an existing mysql record with another value after
calculating it. Below is the code.
?php
$connection = mysql_connect(localhost, root, ) or die(Error
connecting to database);
mysql_select_db(maha, $connection);
$result = mysql_query(SELECT * FROM tax, $connection) or die(error
querying database);
$i = 0;
while($result_ar = mysql_fetch_assoc($result)){
?
tr ?php if($i%2 == 1){ echo class='body2'; }else{echo
class='body1';}?
td
?php
$res=$result_ar['mwool40_totqty']-10;
echo $res;
?/td
/tr
?php
$i+=1;
}
?
I want to append the $mwool40_totqty value with the calculated one $res.
Let say initially the value of $mwool40_totqty is 50. and after calculation
its value became 40, then the code should edit/alter/update the value in
mysql table with integer 40. Please help me with this.
--
*Best,
*
*Guru™*
Re: [PHP-DB] SELECT
Dear Ron If only day is required u could add another day condition in the where clause e.g. month(current_date) between and day(current_date) between. i think u require something more than this. So could u pls explain your requirement in a little more detail say what would be the output of the query given by u. When would u consider the start date/month and when the end one. regds amit The difference between fiction and reality? Fiction has to make sense. On Mon, Oct 17, 2011 at 11:08 PM, Ron Piggott ron.pigg...@actsministries.org wrote: 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
Re: [PHP-DB] SELECT syntax
select casehttp://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html works in mysql also regds amit The difference between fiction and reality? Fiction has to make sense. On Thu, Oct 13, 2011 at 3:26 AM, Jack van Zanen j...@vanzanen.com wrote: Hi In Oracle (and maybe others) you can use select case when answer=1 then trivia_answer_1 when answer=2 then trivia_answer_2 when answer=3 then trivia_answer_3 when answer=4 then trivia_answer_4 else null end answer from bible_trivia_table OR You can select all of them and process in PHP, should not be too hard to come up with a couple of lines of code to display only 1 variable based on the value of variable 5. Overhead should be pretty minimal as well You'll be writing something to display a value anyway Jack van Zanen - This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation On Thu, Oct 13, 2011 at 6:24 AM, Ron Piggott ron.pigg...@actsministries.org wrote: 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 http://www.theverseoftheday.info/
Re: [PHP-DB] SELECT syntax
another examplehttp://mysql-tips.blogspot.com/2005/04/mysql-select-case-example.html regds amit The difference between fiction and reality? Fiction has to make sense. On Thu, Oct 13, 2011 at 9:34 AM, Amit Tandon att...@gmail.com wrote: select casehttp://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html works in mysql also regds amit The difference between fiction and reality? Fiction has to make sense. On Thu, Oct 13, 2011 at 3:26 AM, Jack van Zanen j...@vanzanen.com wrote: Hi In Oracle (and maybe others) you can use select case when answer=1 then trivia_answer_1 when answer=2 then trivia_answer_2 when answer=3 then trivia_answer_3 when answer=4 then trivia_answer_4 else null end answer from bible_trivia_table OR You can select all of them and process in PHP, should not be too hard to come up with a couple of lines of code to display only 1 variable based on the value of variable 5. Overhead should be pretty minimal as well You'll be writing something to display a value anyway Jack van Zanen - This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation On Thu, Oct 13, 2011 at 6:24 AM, Ron Piggott ron.pigg...@actsministries.org wrote: 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 http://www.theverseoftheday.info/
Re: [PHP-DB] Learning bound variables ??
Dear Gevin I believe, u wish to understand about $this-... Actually this is object oriented way of writing the variable. Here this is a key word and refers to the this instance of the object. In simpler words if 2 different sessions are calling this line, then each will pass on the value of reffer variable it has stored. In non object syntax it would be written as header(Location: . $referrer) regds amit The difference between fiction and reality? Fiction has to make sense. On Mon, Aug 29, 2011 at 4:33 PM, Gavin Chalkley gavin.chalk...@gmail.comwrote: Dian, I appreciate what it does and understand the redirect. What I am more refering to is the manner to which it is written On 29/08/2011 12:02, dian.armstr...@gmail.com wrote: It's redirect script on php. Cmiiw --Original Message-- From: Gavin Chalkley To: php-db@lists.php.net Subject: [PHP-DB] Learning bound variables ?? Sent: Aug 29, 2011 5:45 PM Afternoon all. I am learning the base php at the moment and have come across scripting that has the following examples: |header(Location: .$this-referrer); Could someone point me in a direction to learn about this osrt of thing? Or could some explain? Best regards, Gavin|| | Powered by Telkomsel BlackBerry® -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT online store discount %
Ron Have u thought of CASE (in SELECT)http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html. 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
Re: [PHP-DB] Insert query error
Chris
The error is indicated by the keywords a foreign key constraint fails, This
implies that some value in store table is missing which u are trying to
insert in posts. So check for the value u are trying to input in post
table. U have to check for the values of post_store field from the input
list in store table's store_id field
regds
amit
The difference between fiction and reality? Fiction has to make sense.
On Sat, Aug 6, 2011 at 10:11 AM, Chris Stinemetz
chrisstinem...@gmail.comwrote:
I am getting the following error trying to run the below query. Any
suggestions on what I am doing wrong?
Thank you,
Something went wrong while inserting your store visitCannot add or
update a child row: a foreign key constraint fails (`store`.`posts`,
CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`post_store`) REFERENCES
`stores` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE)
$sql = INSERT INTO
posts(market, type, post_store, post_tptest, post_content, post_by,
post_date)
VALUES(' . mysql_real_escape_string($_POST['market']) . ',
' . mysql_real_escape_string($_POST['type']) . ',
' . mysql_real_escape_string($_POST['post_store']) . ',
' . sha1($_POST['post_tptest']) . ',
' . mysql_real_escape_string($_POST['post_content']) . ',
. $_SESSION['user_id'] . ,
NOW());
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] UNION ALL
Dear Ron Try this === 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 === I have removed the first line and the last line of your query regds amit The difference between fiction and reality? Fiction has to make sense. On Sun, Jul 17, 2011 at 10:14 AM, Ron Piggott ron.pigg...@actsministries.org wrote: 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
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.orgwrote: 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] Cant Get Data
Dear Gary
$_SESSION variable would be available only if you have session_start() at
the top of the page. For $_POST, i presume u hve method POST defined for the
form element
regds
amit
The difference between fiction and reality? Fiction has to make sense.
On Mon, Feb 21, 2011 at 10:39 PM, Gary gp...@paulgdesigns.com wrote:
I have a 3 step form that calls the data from a mysql DB. First is a
dropdown to select a state, which then brings up a dynamically created
check
list of the counties in that state, so far all works well. I am unable to
get the checkbox choices to appear on the third page.
If someone could take a look at this and tell me where I am going wrong.
This is the code for the page that creates the checkboxes. There are some
extra lines in there where I was trying to get it to work. Again this works
fine, but my guess is that see this may shed light.
?php
$county1=$_SESSION['county$i'];
$counties=$_SESSION['counties'];
$row_name=$_SESSION['name'] = $row[name];
$count_choice=$_SESSION['name'];
$result1=$_SESSION['$result'];
$choice=$_POST['state'];
if ( isset($_POST['submit']) ) { // if form is submitted, process it
for($i=1; $i=$_POST['counties']; $i++) {
if ( isset($_POST[county$i] ) ) {
print $_POST[county$i]. is checked.br/;
}
}
} else { // if form isn't submitted, output the form
$county_choice=$_SESSION['county$1'];
print form action=\phpForm3.php\ method=\POST\\n;
/*$link = mysql_connect(, , );
mysql_select_db();*/
$result = mysql_query(SELECT * FROM `counties` WHERE state_id =
'$choice')
or die(mysql_error());
if ($result) {
print table width=200 border=1\n;
print thnbsp; /th\n;
print th State /th\n; //2 fields in Counties table, State and County
print th County /th\n;
print /tr\n;
//create table
$i = 0;
while ( $row = mysql_fetch_array($result) ) {
$i++;
print tr\n;
print tdinput type=\checkbox\ name=\county$i\
value=\$row[name]\/td\n;
echo td{$row['state_id']}/td\n;
echo td{$row['name']}/td\n;
echo /tr\n;
}//end while
print /table\n;
} else {
echo(PError performing query: .
mysql_error() . /P);
}
print input type=\hidden\ name=\counties\ value=\$i\/\n;
print input type=\submit\ name=\submit\ value=\Go\/\n;
}
This is the page that I am unable to get to call the checkbox selections
?php
$count_choice1=$_SESSION['county$i'];
$count_choice2=$POST['counties'];
$count_choice3=$_POST['name'];
$result1=$_SESSION['$result'];
/*$count_choice=$_SESSION['name']; */
$count_choice=$_POST['county$i'];
$choice=$_SESSION['name'];
$_POST[county$i];
$result = mysql_query(SELECT * FROM `counties` WHERE name = '$choice') or
die(mysql_error());
/*$query = mysql_query(SELECT * FROM counties );
$result = mysql_query($query) or die (Error in query: $query.
.mysql_error());;
echo $row['name'];
echo $query;*/
while($row = mysql_fetch_array($result))
{
echo $row['name'];
/*{$row['name']}br ;*/
}
echo $choice;
/*echo $_SESSION['county$i'];
echo $_SESSION['counties'];
echo $_SESSION['name'];
echo $count_choice;
echo $row['name'];
echo $county$1;
echo $counties;
echo $row_name; */
?
--
Any Suggestions?
Thank you.
Gary
__ Information from ESET Smart Security, version of virus signature
database 5893 (20110221) __
The message was checked by ESET Smart Security.
http://www.eset.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] NULL values
Dear Ron Or try this SELECT * FROM `paypal_payment_info` WHERE ifnull(os1, '') 'commission_paid' regds amit The difference between fiction and reality? Fiction has to make sense. On Sat, Dec 4, 2010 at 7:55 AM, Ron Piggott ron.pigg...@actsministries.orgwrote: 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
Re: [PHP-DB] If condition in query
My question -
1. why have u used mysqli_fetch_array($result[0]), in place of
mysqli_fetch_array($result)) i.e. without array brackets as $result is not
an array. What is returned by mysqli_fetch_array is an array.
2. Have u checked the your query (print $sq1) after u added *extra test in
the query to exclude blank values;eg, (if(isset ($bmi) '$bmi' = BMI )*,.
If your output works in mysql (mysql client), it would work here also
regds
amit
The difference between fiction and reality? Fiction has to make sense.
On Thu, Nov 18, 2010 at 10:22 PM, Niel Archer n...@chance.now wrote:
Dear list -
Thank you for all your excellent help.
I wish to search a table. In this case, I have five(5) columns:
site, Record, BMI, Weight and Height.
I wish to be able to search on one or more of the columns. If I use
a query like:
$ste = $_POST['site'];
$req = $_POST['Record'];
$wgt = $_POST['Weight'];
$hgt = $_POST['Height'];
$bmi = $_POST['BMI'];
$sql1 = select * from intake2 where site = '$ste' Weight =
'$wgt' Record = '$req' '$hgt' = Height '$bmi' = BMI ;
$result = mysqli_query($cxn, $sql1);
and do not use all the variables, no data is returned. I use to
extract the data from the query.
while($row = mysqli_fetch_array($result[0]))
{
$site2 = $row[0];
$record2 = $row[1];
$bmi2 = $row[2];
$wgt2 = $row[3];
$hgt2 = $row[4];
printf(%s\t%d\t%3.1f\t%d\t%dbr /, $site2, $record2,
$bmi2, $wgt2, $hgt2);
}
If I put an extra test in the query to exclude blank values;eg,
(if(isset ($bmi) '$bmi' = BMI ), $result defaults to a boolean
and mysqli_fetch_array($result) fails. I wish to be able to search
on one or more, but not necessarily all, of the parameters and be
able to output the results.
Advice and help please.
Thanks in advance.
Ethan
First you need to protect your input from injection and other attacks.
http://en.wikipedia.org/wiki/SQL_injection
for the problem you ask, I'd suggest building the query in php rather
than SQL A simple example would be:
$where ' ';
if (isset($ste)) {
$where .= site = '$ste';
}
if (isset($wgt)) {
$where .= , Weight = '$wgt';
}
$sql .= WHERE $where;
--
Niel Archer
niel.archer (at) blueyonder.co.uk
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Is this syntax is correct?
SELECT d.username, r.password FROM data join registration r on r.username = d.username WHERE r.username like '%s' AND r.password like '%s' Presuming the password stored as clear text and username is common field SELECT d.username, r.password FROM data join registration r on r.username = d.username WHERE r.username like '%s' AND r.password like '%s' or SELECT d.username FROM data as d, registration as r WHERE r.username like '%s' AND and r.password like '%s' and r.username = d.username regds amit The difference between fiction and reality? Fiction has to make sense. On Sun, Sep 5, 2010 at 6:48 PM, nagendra prasad nagendra802...@gmail.comwrote: Hi All, Is this syntax is correct?? SELECT username FROM data, password FROM registration WHERE username=%s AND password=%s Best, Guru.
Re: [PHP-DB] Need Help with data sorting
Hello,
I a not clear why are u using so many tables, and also div's within the
table. Either use tableless (css tables ) or simply use table(preferably
one) without divs in it
regds
amit
The difference between fiction and reality? Fiction has to make sense.
On Sun, Jan 31, 2010 at 3:45 PM, nagendra prasad
nagendra802...@gmail.comwrote:
Hi,
I have a database of MP3s in mysql and I have created a php search engine.
Now I want to sort it in ascending or descending order if user clicks on
the
title of the table. For example if user want to arrange the table in
ascending or descending order by Name, Size, or by any other field. Also, I
am unable to arrange the table properly. Please help me?? Below is the
code.
Type = Rock, Pop etc.
Url = file url
Name= Name of the song
Size = size
?php
//get data
$button = $_GET['submit'];
$search = $_GET['search'];
$s = $_GET['s'];
if (!$s)
$s = 0;
$e = 30; // Just change to how many results you want per page
$next = $s + $e;
$prev = $s - $e;
if (strlen($search)=2)
echo Must be greater then 3 chars;
else
{
echo br /tabletrtdfont face='sana-serif' size='10'font
color='blue' bMymp3/b/bfont face='sana-serif'
size='3'reg;/tdtdform action='search.php' method='GET'input
type='text' onclick=value='' size='50' name='search' value='$search'
input
type='submit' name='submit' value='Search'/form/td/tr/table;
//connect to database
mysql_connect(localhost,root,);
mysql_select_db(mp3);
//explode out search term
$search_exploded = explode( ,$search);
foreach($search_exploded as $search_each)
{
//construct query
$x++;
if ($x==1)
$construct .= name LIKE '%$search_each%';
else
$construct .= OR name LIKE '%$search_each%';
}
//echo outconstruct
$constructx = SELECT * FROM mp3 WHERE $construct;
$construct = SELECT * FROM mp3 WHERE $construct ORDER BY se DESC LIMIT
$s,$e ;
$run = mysql_query($constructx);
$foundnum = mysql_num_rows($run);
$run_two = mysql_query($construct);
if ($foundnum==0)
echo No results found for b$search/b;
else
{
echo table bgcolor='#FF' width='100%' height='1px'br
//tabletable bgcolor='#f0f7f9' width='100%' height='10px'trtddiv
align='right'Showing 1-20 of b$foundnum/b results found for
b$search./b/div/td/tr/tablep;
echo table bgcolor='#FF' width='100%' height='1px'br
//tabletable bgcolor='#f0f7f9' width='100%' height='10px'trtdfont
face='sana-serif' size='3'
nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;Type
bnbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;
Name/b
nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;
nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;
nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; Size
/div/td/tr/tablep;
while ($runrows = mysql_fetch_assoc($run_two))
{
//get data
$type = $runrows['type'];
$date = $runrows['date'];
$url = $runrows['url'];
$name = $runrows['name'];
$size = $runrows['size'];
print 'table width=800 border=0 align=center ';
print 'tdfont color=blue'.$type.'/font/td';
//print 'tdfont color=blue'.http://localhost/mymp3/.;a
herf='$url'$name.'/font/td';
print 'tdfont color=blue'.a href='$url'$name.'/font/td';
print 'tdfont color=blue'.$size.'/font/td';
print '/tr';
print '/tr';
print '/table';
/* echo table width='300px' height='10px'
h4 font color='blue'$type -- $name/fontbbrSize: $size
/b/abr /
font color='00CC00'Date Added: $date/font/table/h4
; */
}
?
table width='100%'
tr
td
div align=center
brbr
?php
if (!$s=0)
echo a href='search.php?search=$searchs=$prev'Prev/a;
$i =1;
for ($x=0;$x$foundnum;$x=$x+$e)
{
echo a href='search.php?search=$searchs=$x'$i/a ;
$i++;
}
if ($s$foundnum-$e)
echo a href='search.php?search=$searchs=$next'Next/a;
}
}
?
/div
/td
/tr
/table
--
Guru Prasad
Ubuntu Voice GTK+ Forum
