Re: [PHP] MySQL select matching
On Mon, 19 Jul 2010 10:36:40 -0600 Ashley M. Kirchner ash...@pcraft.com wrote: mysql select * from table where id='1'; +---+-+-+---+ | 1 | 123 | 0.0 | C | | 1 | 234 | 0.1 | D | | 1 | 345 | 0.0 | D | | 1 | 456 | 0.1 | C | | 1 | 567 | 0.1 | G | +---+-+-+---+ Now, I have to find other IDs that match the above result. In the table, that would be ID '3' (and in the entire DB, there may be others as well - I need to find all those IDs.) But, notice how ID 0003 isn't in the same order as ID 1, but the data is still the same. select distinct id from `table` where concat(`b`, `c`, `d`) in (select concat(`b`,`c`,`d` from `table` where id = '0001') AND id != '0001'; (untested) -- Simcha Younger sim...@syounger.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL select matching
I may be going at this completely wrong but at the moment I'm stuck. I have a DB from a client and need to do several searches on it. This one sentence is important because it's their DB, not mine. So I can't modify the way the DB was created in the first place, I can only work with what I have. And, whatever the solution to this might be, it does NOT have to be strictly MySQL, it can also be a PHP solution (which is why I'm sending it there as well.) So, having said that, consider the following table: +---+-+-+---+ | 1 | 123 | 0.0 | C | | 1 | 234 | 0.1 | D | | 1 | 345 | 0.0 | D | | 1 | 456 | 0.1 | C | | 1 | 567 | 0.1 | G | | 2 | 123 | 0.0 | C | | 2 | 234 | 0.1 | D | | 2 | 345 | 0.0 | D | | 3 | 234 | 0.1 | D | | 3 | 345 | 0.0 | D | | 3 | 123 | 0.0 | C | | 3 | 456 | 0.1 | C | | 3 | 567 | 0.1 | G | | 4 | 123 | 0.0 | C | | 4 | 234 | 0.1 | D | | 4 | 345 | 0.0 | D | +---+-+-+---+ mysql select * from table where id='1'; +---+-+-+---+ | 1 | 123 | 0.0 | C | | 1 | 234 | 0.1 | D | | 1 | 345 | 0.0 | D | | 1 | 456 | 0.1 | C | | 1 | 567 | 0.1 | G | +---+-+-+---+ Now, I have to find other IDs that match the above result. In the table, that would be ID '3' (and in the entire DB, there may be others as well - I need to find all those IDs.) But, notice how ID 0003 isn't in the same order as ID 1, but the data is still the same. So how do I efficiently search through the DB to find other IDs that matches the one I need? I can't imagine doing a for loop selecting each ID and comparing their result to the one I'm starting with. If the DB contains thousands upon thousands of rows, that might take a very long time. Open to suggestions. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: [php] [mysql] select and subselect
Allen McCabe wrote: I have a page on my site where I can optionaly filter by certain fields (order by filesize or file category), but I am implementing a shopping cart type of idea where users can submit an order. As administrators, my coworkers and I need to be able to filter orders by their contents. For example: View all orders for Jack and the Beanstalk, where an order may have Jack and the Beanstalk and other items. I have an order table that keeps track of the order_id, the date, the status, etc. I also have an order_lineitem table that is the contents of the order. This has a one-to-many structure (without foreign keys because it is mysql). I was baffled as to how to filter the orders by the item_id that appears in the order_lineitem table. I just came up with this, but I'm not sure how the mysql_queries will handle an array. Do I have to do some extensive regular expression management here to get this to work, or will it accept an array? ?php if (isset($_POST['showid'])) $showid = $_POST['showid']; $subSQL = SELECT order_id FROM afy_show_lineitem WHERE show_id = {$_POST['showid']};; $subResult = mysql_query($subSQL); $where = WHERE; $extQuery = 'order_id = {$subResult}'; } $resultOrders = mysql_query(SELECT * FROM afy_order {$where} {$extQuery};) or die(mysql_error(Could not query the database!)); ? If $_POST['showid'] is likely to be an array, use implode to create a comma separated string of values, then use IN to build the query. $ids = implode(',', $_POST['showid']; $subSQL = SELECT order_id FROM afy_show_lineitem WHERE show_id IN ($ids) But you need also to check your logic - your query above will have two WHERE in it and will fail miserably :-) And your call to mysql_error will probably not help you either, as the (optional) argument should be a link identifier, not a string of your choosing. Cheers -- David Robley MS-DOS: celebrating ten years of obsolescence Today is Sweetmorn, the 29th day of The Aftermath in the YOLD 3175. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: [php] [mysql] select and subselect
On Mon, 16 Nov 2009 14:21:41 -0800, Allen McCabe wrote: I have an order table that keeps track of the order_id, the date, the status, etc. I also have an order_lineitem table that is the contents of the order. This has a one-to-many structure (without foreign keys because it is mysql). http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html InnoDB supports foreign key constraints. ... I was baffled as to how to filter the orders by the item_id that appears in the order_lineitem table. I just came up with this, but I'm not sure how the mysql_queries will handle an array. Do I have to do some extensive regular expression management here to get this to work, or will it accept an array? ?php if (isset($_POST['showid'])) $showid = $_POST['showid']; $subSQL = SELECT order_id FROM afy_show_lineitem WHERE show_id = {$_POST['showid']};; $subResult = mysql_query($subSQL); $where = WHERE; $extQuery = 'order_id = {$subResult}'; } $resultOrders = mysql_query(SELECT * FROM afy_order {$where} {$extQuery};) or die(mysql_error(Could not query the database!)); [[ Note: You should probably have posted to the php-db list (or the php.db newsgroup) to increase the chance of getting responses from people who actually know what they're talking about. I'm a MySQL newbie... ]] $subResult is a resource. You need to fetch the results before you can use them like this. Maybe something like: /* UNTESTED */ /* Don't forget to escape! Some will argue that you are better off using prepared statements. */ $showid = mysql_real_escape_string ($_POST['showid']); $q = _ SELECT `order_id` FROM `afy_show_lineitem` WHERE `show_id` = '$showid'; _; $res = mysql_query ($q); if ($res) { while ($row = mysql_fetch_row ($res)) { $ids[] = $row[0]; /* Assuming numerical ids. */ } $ids_spec = implode (',', $ids); mysql_free_result ($res); } else { /* Handle error */ } if (isset ($ids_spec)) { $q = _ SELECT * FROM `afy_order` WHERE `order_id` IN ($ids_spec) _; $res = mysql_query ($q); if ($res) { /* Do stuff */ } else { /* Handle error */ } } But you could also do it in one query (*untested*): /* UNTESTED */ SELECT `ao`.* FROM `afy_order` AS `ao`, `afy_show_lineitem` AS `asl` WHERE `ao`.`show_id` = $show_id AND `asl`.`order_id` = `ao`.`show_id` Or maybe there are better ways, such as using INNER JOIN, but those sometimes end up using a temporary table... (Hint: php-db, php.db, php-db, ...) /Nisse -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] [php] [mysql] select and subselect
I have a page on my site where I can optionaly filter by certain fields (order by filesize or file category), but I am implementing a shopping cart type of idea where users can submit an order. As administrators, my coworkers and I need to be able to filter orders by their contents. For example: View all orders for Jack and the Beanstalk, where an order may have Jack and the Beanstalk and other items. I have an order table that keeps track of the order_id, the date, the status, etc. I also have an order_lineitem table that is the contents of the order. This has a one-to-many structure (without foreign keys because it is mysql). I was baffled as to how to filter the orders by the item_id that appears in the order_lineitem table. I just came up with this, but I'm not sure how the mysql_queries will handle an array. Do I have to do some extensive regular expression management here to get this to work, or will it accept an array? ?php if (isset($_POST['showid'])) $showid = $_POST['showid']; $subSQL = SELECT order_id FROM afy_show_lineitem WHERE show_id = {$_POST['showid']};; $subResult = mysql_query($subSQL); $where = WHERE; $extQuery = 'order_id = {$subResult}'; } $resultOrders = mysql_query(SELECT * FROM afy_order {$where} {$extQuery};) or die(mysql_error(Could not query the database!)); ?
[PHP] mysql select statement in php having three conditions
hi, can someone tell me how to do this: i have to retrive data from a mysql table let's sayTABLE . i have to check that the rows i retrive meet this condition: field1='$variable',field2 is false and field3 is also false. as you can see field2 and field3 are bool type. field1 is varchar. i did this query SELECT * FROM TABLE WHERE field1='$variable' AND field2='0' AND field3='0'; another one tried is SELECT * FROM TABLE WHERE field1='$variable' AND field2=false AND field3=false; But none was a success. it didn't matter whether i used '0' or 0 and false or 'false'. thanks in advance. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql select statement in php having three conditions
[snip] can someone tell me how to do this: i have to retrive data from a mysql table let's sayTABLE . i have to check that the rows i retrive meet this condition: field1='$variable',field2 is false and field3 is also false. as you can see field2 and field3 are bool type. field1 is varchar. i did this query SELECT * FROM TABLE WHERE field1='$variable' AND field2='0' AND field3='0'; another one tried is SELECT * FROM TABLE WHERE field1='$variable' AND field2=false AND field3=false; But none was a success. it didn't matter whether i used '0' or 0 and false or 'false'. thanks in advance. [/snip] That is likely because the data is either blank or NULL , try; SELECT * FROM TABLE WHERE field1='$variable' AND field2 IS NULL AND field3= IS NULL ; or SELECT * FROM TABLE WHERE field1='$variable' AND field2='' AND field3=''; -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] mysql select and date functions
i have a field in a table called Posted and it is a timestamp type im using mysql and my page is supposed to take the timestamp field and show it on the screen in the form of month#/dayofmonth/year like 02/18/2003 the problem im having is trying to get it to be formatted.. i tried mysql functions like dayofmonth(Posted) month(Posted) and year(Posted) in the query: $query=mysql_query(select dayofmonth(Posted), month(Posted), year(Posted), hour(Posted), minute(Posted), Name, Prayer from prayer); this query works just fine but my question is now in php how to display the results for dayofmonth(Posted) and so on further code follows: while($view=mysql_fetch_array($query)) { //display variables from query here... //get to the date functions: //$view[month(Posted)]/... doesnt work //php complains about missing ] and this doesnt work //either while($view=mysql_fetch_object($query)){ //show variables here.. //show date fields in table... //$view-dayofmonth(Posted) doesnt work either all i get //in display is (Posted) ...rest of code here... ? and its strange because if i try: echo $view-dayofmonth(Posted); i get parse error call to undefined function dayofmonth() in file.php on line (line #) any ideas on how to get this to work? --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.443 / Virus Database: 248 - Release Date: 1/10/2003 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql select and date functions
Change your query: select dayofmonth(Posted) as DOM_Posted, month(Posted) as M_Posted, year(Posted)as Y_Posted, hour(Posted) as HH_Posted, minute(Posted) as MM_Posted, Name, Prayer from prayer Then access the access the fields by the name (DOM_Posted). $recSet = mysql_query($query); $row = mysql_fetch_assoc($recSet); Access individual field: $row['DOM_Posted']; $row['M_Posted']; etc OR Use the php date (Don't know - haven't tried) functions to parse the required values out of the Posted field. HTH, Bryan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql select and date functions
tnx it worked just like i wanted it to... - Original Message - From: Barajas, Arturo [EMAIL PROTECTED] To: 'Sunfire' [EMAIL PROTECTED] Sent: Tuesday, February 18, 2003 7:59 PM Subject: RE: [PHP] mysql select and date functions Have you tried naming the fields? $query=mysql_query(select dayofmonth(Posted) AS day, month(Posted) AS Mo, year(Posted) AS yr, hour(Posted) as hr, minute(Posted) as min, Name, Prayer from prayer); and the use echo $view-day . / . $view-mo . / . $view-yr . br\n; or something similar? -- Un gran saludo/Big regards... Arturo Barajas, IT/Systems PPG MX (SJDR) (427) 271-9918, x448 -Original Message- From: Sunfire [mailto:[EMAIL PROTECTED]] Sent: Martes, 18 de Febrero de 2003 06:51 p.m. To: [EMAIL PROTECTED] Subject: [PHP] mysql select and date functions i have a field in a table called Posted and it is a timestamp type im using mysql and my page is supposed to take the timestamp field and show it on the screen in the form of month#/dayofmonth/year like 02/18/2003 the problem im having is trying to get it to be formatted.. i tried mysql functions like dayofmonth(Posted) month(Posted) and year(Posted) in the query: $query=mysql_query(select dayofmonth(Posted), month(Posted), year(Posted), hour(Posted), minute(Posted), Name, Prayer from prayer); this query works just fine but my question is now in php how to display the results for dayofmonth(Posted) and so on further code follows: while($view=mysql_fetch_array($query)) { //display variables from query here... //get to the date functions: //$view[month(Posted)]/... doesnt work //php complains about missing ] and this doesnt work //either while($view=mysql_fetch_object($query)){ //show variables here.. //show date fields in table... //$view-dayofmonth(Posted) doesnt work either all i get //in display is (Posted) ...rest of code here... ? and its strange because if i try: echo $view-dayofmonth(Posted); i get parse error call to undefined function dayofmonth() in file.php on line (line #) any ideas on how to get this to work? --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.443 / Virus Database: 248 - Release Date: 1/10/2003 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.443 / Virus Database: 248 - Release Date: 1/10/2003 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql select and date functions
Change your query: selectdayofmonth(Posted) as DOM_Posted, month(Posted) as M_Posted, year(Posted)as Y_Posted, hour(Posted) as HH_Posted, minute(Posted) as MM_Posted, Name, Prayer from prayer Then access the access the fields by the name (DOM_Posted). $recSet = mysql_query($query); $row = mysql_fetch_assoc($recSet); Access individual field: $row['DOM_Posted']; $row['M_Posted']; etc OR Use the php date (Don't know - haven't tried) functions to parse the required values out of the Posted field. Or you can use the DATE_FORMAT() function in your query. SELECT DATE_FORMAT(Posted,'%m/%d/%Y') as F_Posted FROM Prayer Then you can echo $row['F_Posted'] and it'll already be in the correct format. Check the manual for the correct usage of DATE_FORMAT() (The MySQL manual.) ---John W. Holmes... PHP Architect - A monthly magazine for PHP Professionals. Get your copy today. http://www.phparch.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Mysql Select
Hello, Can anyone help me with this simple query: I want to limit to 1 the same names returned from the query like: FROM: SELECT gallery.design FROM gallery buffet buffet buffet buffet barstools barstools barstools barstools barstools barstools barstools barstools tables tables tables TO: SELECT gallery.design FROM gallery ?? buffet barstools tables THANKS !! Andras Kende [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Select
SELECT DISTINCT(gallery.design) FROM gallery : I want to limit to 1 the same names returned from the query like: [CUT] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL select a field's first X bytes
Is there a way in MySQL to select a fields first X bytes? I have a field of type text. I would like to print the first 500 or so bytes of its contents as a summary of the entire field, then having the user click read more... or something of that sort for the whole document. I read up on select in the MySQL manual and didn't see much, does anyone out there know of such a feature? Should I just take the whole field into php and do it that way? thanks, Micah Bushouse -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL select a field's first X bytes
What's wrong with select left(myfield, 500) from mytable; ? Quentin -Original Message- From: Micah Bushouse [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 17 December 2002 3:22 p.m. To: [EMAIL PROTECTED] Subject: [PHP] MySQL select a field's first X bytes Is there a way in MySQL to select a fields first X bytes? I have a field of type text. I would like to print the first 500 or so bytes of its contents as a summary of the entire field, then having the user click read more... or something of that sort for the whole document. I read up on select in the MySQL manual and didn't see much, does anyone out there know of such a feature? Should I just take the whole field into php and do it that way? thanks, Micah Bushouse -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL select a field's first X bytes
Thanks a lot! I'm just blind! Quentin Bennett [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] o.nz... What's wrong with select left(myfield, 500) from mytable; ? Quentin -Original Message- From: Micah Bushouse [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 17 December 2002 3:22 p.m. To: [EMAIL PROTECTED] Subject: [PHP] MySQL select a field's first X bytes Is there a way in MySQL to select a fields first X bytes? I have a field of type text. I would like to print the first 500 or so bytes of its contents as a summary of the entire field, then having the user click read more... or something of that sort for the whole document. I read up on select in the MySQL manual and didn't see much, does anyone out there know of such a feature? Should I just take the whole field into php and do it that way? thanks, Micah Bushouse -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Newbie: PHP/MySQL (SELECT)
if one of $titulotxt or $cdstxt is not set, your query will look something like this: SELECT * FROM divx WHERE cds like '$cdstxt' ORDER BY titulo As you see, there is unnecessery . I build my search queries using this form: $query_cond=''; foreach($_GET as $col = $val) { switch($col) { case 'cds': // you can add more cases here for conditions that need to be exact $query_cond .= $col LIKE '$val' AND ; break; case 'titulo': // you can add more cases here for conditions that need not to be exact $query_cond .= $col LIKE '%$val%' AND ; break; } } // get rid of final AND $query_cond = substr($query_cond, 0, strlen($query_cond) - 4); // and as you don't have any other conditions, $query_cond cannot be empty - we would have excessive WHERE // so if it is empty, make it 1 if($query_cond=='') $query_cond='1'; $sql=SELECT * FROM divx WHERE $query_cond ORDER BY titulo; Mr. BuNgL3 wrote: Hi... I'm with a little sintax problem... The question is that i have two search fields (titulotxt and cdstxt) and i want to create an mysql condition... i trying: $sql1=($titulotxt) ? titulo like '%.$titulotxt.%':; $sql2=($cdstxt) ? cds like '$cdstxt':; $sql=SELECT * FROM divx WHERE .$sql1.$sql2 ORDER BY titulo; but he's giving me a sintax error on the 3 line... Can anyone teach me how i must do to validate the mysql condition and make it work? Thanks -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Newbie: PHP/MySQL (SELECT)
On Thursday 21 November 2002 03:53, Mr. BuNgL3 wrote: Hi... I'm with a little sintax problem... The question is that i have two search fields (titulotxt and cdstxt) and i want to create an mysql condition... i trying: $sql1=($titulotxt) ? titulo like '%.$titulotxt.%':; $sql2=($cdstxt) ? cds like '$cdstxt':; $sql=SELECT * FROM divx WHERE .$sql1.$sql2 ORDER BY titulo; but he's giving me a sintax error on the 3 line... Can anyone teach me how i must do to validate the mysql condition and make it work? Try: $sql=SELECT * FROM divx WHERE .$sql1..$sql2. ORDER BY titulo; -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* He thinks by infection, catching an opinion like a cold. */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Newbie: PHP/MySQL (SELECT)
On Thursday 21 November 2002 03:53, Mr. BuNgL3 wrote: Hi... I'm with a little sintax problem... The question is that i have two search fields (titulotxt and cdstxt) and i want to create an mysql condition... i trying: $sql1=($titulotxt) ? titulo like '%.$titulotxt.%':; $sql2=($cdstxt) ? cds like '$cdstxt':; $sql=SELECT * FROM divx WHERE .$sql1.$sql2 ORDER BY titulo; but he's giving me a sintax error on the 3 line... Can anyone teach me how i must do to validate the mysql condition and make it work? Try: $sql=SELECT * FROM divx WHERE .$sql1..$sql2. ORDER BY titulo; and add a space after the WHERE. Also consider the positioning of the single quotes (') and double-quotes () in the $sql1 assignment statement - they must be nested. Finally, consider echoing sql1, $sql2, and $sql to be able to see with your own eyes! =dn -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Newbie: PHP/MySQL (SELECT)
$sql1 = titulo like '%$titulotxt%' $sql2 = cd like '$cdstxt'; $sql = SELECT * FROM divx WHERE $sql1 AND $sql2 ORDER BY titulo; I think you are getting the error because of the ($titulotxt) ? portion of your sql statements. You really only need the two statements listed above and then put into your actual sql statement. Robbert van Andel -Original Message- From: Mr. BuNgL3 [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 20, 2002 11:54 AM To: [EMAIL PROTECTED] Subject: [PHP] Newbie: PHP/MySQL (SELECT) Hi... I'm with a little sintax problem... The question is that i have two search fields (titulotxt and cdstxt) and i want to create an mysql condition... i trying: $sql1=($titulotxt) ? titulo like '%.$titulotxt.%':; $sql2=($cdstxt) ? cds like '$cdstxt':; $sql=SELECT * FROM divx WHERE .$sql1.$sql2 ORDER BY titulo; but he's giving me a sintax error on the 3 line... Can anyone teach me how i must do to validate the mysql condition and make it work? Thanks -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from all computers.
Re: [PHP] Newbie: php/mysql (Select)
At 20:23 20.11.2002, Mr. BuNgL3 said: [snip] Hi... I'm with a little sintax problem... The question is that i have two search fields (titulotxt and cdstxt) and i want to create an mysql condition... i trying: $sql1=($titulotxt) ? titulo like '%.$titulotxt.%':; $sql2=($cdstxt) ? cds like '$cdstxt':; $sql=SELECT * FROM divx WHERE .$sql1.$sql2 ORDER BY titulo; but the bastard is giving me a sintax error on the 3 line... Can anyone teach me how i must do to validate the mysql condition and make it work? [snip] As already said, put a space after the WHERE clause. What happens if $sql1 or $sql2 are empty (as your example provisons)? Create an $sql3 that combines $sql1 and $sql2, and construct your SQL accordingly: $sql1 = ($titulotxt ? null : titulo like '%$titulotxt%'); $sql2 = ($cdstxt? null : cds like '$cdstxt'); $sql3 = $sql1 . ($sql1 $sql2 ? ' AND ' : null) . $sql2; $sql = 'SELECT * FROM divx ' . ($sql3 ? WHERE $sql3 : null) . 'ORDER BY titulo'; -- O Ernest E. Vogelsinger (\)ICQ #13394035 ^ http://www.vogelsinger.at/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] mySQL select statement with mulitple where definitions
someone here has to know the syntax for using multiple items in a where definition, something like: $values = 120106,120095; $sql = SELECT * FROM products WHERE catalognumber = $values ORDER BY price; Does this make sense, » Michael Krisher [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] mySQL select statement with mulitple where definitions
on 1/16/02 11:57 AM, Mike Krisher at [EMAIL PROTECTED] wrote: someone here has to know the syntax for using multiple items in a where definition, something like: $values = 120106,120095; $sql = SELECT * FROM products WHERE catalognumber = $values ORDER BY price; select * from products where catalognumber in (120106,120095) order by you may need quotes around the stuff in the parens, ('120106','120095') -- mike cullerton michaelc at cullerton dot com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] MySQL: Select ALL
but no luck. Take a look at one of the tutorials.. Many can be found on the links page you can click to on www.php.net This will help you to find the info you need and also answer the next question you're going to ask. Bye, B. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] MySQL: Select ALL
Hello Tarrant, Thursday, August 09, 2001, 1:14:25 PM, you wrote: TC When saying; mysql_query(SELECT username FROM users,$db); it only show's TC one of the first users in the column users. TC Could someone please tell me how to make it print out the complete column TC list of usernames. I have tried mysql_query(SELECT username(*) FROM TC users,$db); - but no luck. For example: $query=mysql_query(SELECT username FROM users,$db); while ($row=mysql_fetch_array($query)) { echo $row[username]; } And B. van Ouwerkerk is right with his suggestion about manuals and archive. I know that this problem was here a lot of times. But anyway I hope it helps -- Best regards, Danielmailto:[EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] MySQL: Select ALL
if(!($result=mysql_db_query(SELECT username FROM users $db))) { print Error getting rows; exit(); } while($row=mysql_fetch_array($result)) { $username=$row[username]; print $usernamebr; } mysql_free_result($result); kp == Karl Phillipson PHP SQL Programmer Saffron Hill Ventures 67 Clerkenwell Road London EC1R 5BL Saffron Hill: 0207 693 8300 Direct Line: 0207 693 8318 -Original Message- From: Tarrant Costelloe [mailto:[EMAIL PROTECTED]] Sent: 09 August 2001 12:14 To: '[EMAIL PROTECTED]' Subject: [PHP] MySQL: Select ALL When saying; mysql_query(SELECT username FROM users,$db); it only show's one of the first users in the column users. Could someone please tell me how to make it print out the complete column list of usernames. I have tried mysql_query(SELECT username(*) FROM users,$db); - but no luck. Thanks Taz -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] MySQL Select
Because you can also do: if ($array_result = mysql_fetch_array($result)) { do { // do your thing here } while ($array_result = mysql_fetch_array($result)); } else { echo no records found!; } Cheers Jon -Original Message- From: Chris Lee [mailto:[EMAIL PROTECTED]] Sent: 23 May 2001 21:28 To: [EMAIL PROTECTED] Subject: Re: [PHP] MySQL Select why not just simply it more while ($r = mysql_fetch_array($result)) { // do your thing here } -- Chris Lee [EMAIL PROTECTED] Jon Haworth [EMAIL PROTECTED] wrote in message 67DF9B67CEFAD4119E4200D0B720FA3F53FA57@BOOTROS">news:67DF9B67CEFAD4119E4200D0B720FA3F53FA57@BOOTROS... Something like: if ($array_result = mysql_fetch_array($result)) { do { // do your thing here } while ($array_result = mysql_fetch_array($result)); } HTH Jon -Original Message- From: Jamie Thompson [mailto:[EMAIL PROTECTED]] Sent: 23 May 2001 14:33 To: [EMAIL PROTECTED] Subject: [PHP] MySQL Select ok...sorry to ask a stupid question but $result = mysql_query(SELECT jazz FROM funk WHERE foo='bar') but where do you go from there. How would you iterate through the array a row at a time, doing your thing as you go along? thanks, jamie -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] ** 'The information included in this Email is of a confidential nature and is intended only for the addressee. If you are not the intended addressee, any disclosure, copying or distribution by you is prohibited and may be unlawful. Disclosure to any party other than the addressee, whether inadvertent or otherwise is not intended to waive privilege or confidentiality' ** -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] MySQL Select
ok...sorry to ask a stupid question but $result = mysql_query(SELECT jazz FROM funk WHERE foo='bar') but where do you go from there. How would you iterate through the array a row at a time, doing your thing as you go along? thanks, jamie -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] Re:[PHP] MySQL Select
think this is what you want $i = 0; $result = mysql_query(SELECT jazz FROM funk WHERE foo='bar') while($music = mysql_fetch_array($result)) { $funk_music[$i] = $music[0]; $I++; } ok...sorry to ask a stupid question but $result = mysql_query(SELECT jazz FROM funk WHERE foo='bar') but where do you go from there. How would you iterate through the array a row at a time, doing your thing as you go along? thanks, jamie
RE: [PHP] MySQL Select
Something like: if ($array_result = mysql_fetch_array($result)) { do { // do your thing here } while ($array_result = mysql_fetch_array($result)); } HTH Jon -Original Message- From: Jamie Thompson [mailto:[EMAIL PROTECTED]] Sent: 23 May 2001 14:33 To: [EMAIL PROTECTED] Subject: [PHP] MySQL Select ok...sorry to ask a stupid question but $result = mysql_query(SELECT jazz FROM funk WHERE foo='bar') but where do you go from there. How would you iterate through the array a row at a time, doing your thing as you go along? thanks, jamie -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] ** 'The information included in this Email is of a confidential nature and is intended only for the addressee. If you are not the intended addressee, any disclosure, copying or distribution by you is prohibited and may be unlawful. Disclosure to any party other than the addressee, whether inadvertent or otherwise is not intended to waive privilege or confidentiality' ** -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] MySQL Select
Hi This is what I always do... 1. for one row (saved in array $reihe)... $bla = SELECT * FROM table WHERE id = '$something'''; // db is your database connection $test = mysql_query($bla, $db); // you dont have to use the if...i do that to check if anything was returned if ($reihe = mysql_fetch_row($test)) { echo $reihe[0] . $reihe[1]; } //this will output field one and two of the queried row 2. to get several rows in an array: $bla = SELECT * FROM table'''; // db is your database connection $test = mysql_query($bla, $db); while ($reihe = mysql_fetch_array($test)) { echo $reihe[0]; } //this will output the first field of all rows in this table - Nessi - (heh my first contribution to the mailing list! woohooo) ;))) At 14:32 23/05/01 +0100, you wrote: ok...sorry to ask a stupid question but $result = mysql_query(SELECT jazz FROM funk WHERE foo='bar') but where do you go from there. How would you iterate through the array a row at a time, doing your thing as you go along? thanks, jamie -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] MySQL Select
why not just simply it more while ($r = mysql_fetch_array($result)) { // do your thing here } -- Chris Lee [EMAIL PROTECTED] Jon Haworth [EMAIL PROTECTED] wrote in message 67DF9B67CEFAD4119E4200D0B720FA3F53FA57@BOOTROS">news:67DF9B67CEFAD4119E4200D0B720FA3F53FA57@BOOTROS... Something like: if ($array_result = mysql_fetch_array($result)) { do { // do your thing here } while ($array_result = mysql_fetch_array($result)); } HTH Jon -Original Message- From: Jamie Thompson [mailto:[EMAIL PROTECTED]] Sent: 23 May 2001 14:33 To: [EMAIL PROTECTED] Subject: [PHP] MySQL Select ok...sorry to ask a stupid question but $result = mysql_query(SELECT jazz FROM funk WHERE foo='bar') but where do you go from there. How would you iterate through the array a row at a time, doing your thing as you go along? thanks, jamie -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] ** 'The information included in this Email is of a confidential nature and is intended only for the addressee. If you are not the intended addressee, any disclosure, copying or distribution by you is prohibited and may be unlawful. Disclosure to any party other than the addressee, whether inadvertent or otherwise is not intended to waive privilege or confidentiality' ** -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] MySQL Select
On Mié 23 May 2001 16:32, you wrote: ok...sorry to ask a stupid question but $result = mysql_query(SELECT jazz FROM funk WHERE foo='bar') but where do you go from there. How would you iterate through the array a row at a time, doing your thing as you go along? Man, Read The F** Manuales my_fetch_row, my_fetch_object, etc Saludos :-) -- Cualquiera administra un NT. Ese es el problema, que cualquiera administre. - Martin Marques |[EMAIL PROTECTED] Programador, Administrador | Centro de Telematica Universidad Nacional del Litoral - -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] php/mySql SELECT where clause using dates.
Take out the order by clause or chaqnge it. You can't order by a column you aren't selecting. - Original Message - From: Dan Eskildsen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 03, 2001 10:30 AM Subject: [PHP] php/mySql SELECT where clause using dates. ***NEWBIE ALERT*** (I am only very new at this) PHP and MySQL OK - I have a table with data and one of the fields is a Date field. Now I would like to SELECT all records where the date is less that 45 days old. Eh, How do I do that? I have tried this: #Declaring variables $today=date(Y-m-d); $lastmonth = $today-30 $query = SELECT * FROM customers WHERE Site='egebjergnet' AND DatoOprettet'$lastmonth' ORDER BY FirmaNavn; AND I get an error, of course. hmmm -- == Regards from Denmark, Europe Please cc your reply to [EMAIL PROTECTED] == I haven't lost my mind, I've got it backed up on TAPE somewhere... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] php/mySql SELECT where clause using dates. ooops
i'm sorry, I looked at the wrong part. You need to use the PHP functions to convert your date to a timestamp, then subtract 30 days from it, then reconvert to a MySQL date format. Jon - Original Message - From: Jon Rosenberg [EMAIL PROTECTED] To: Dan Eskildsen [EMAIL PROTECTED] Cc: PHP General List [EMAIL PROTECTED] Sent: Thursday, May 03, 2001 10:28 AM Subject: Re: [PHP] php/mySql SELECT where clause using dates. Take out the order by clause or chaqnge it. You can't order by a column you aren't selecting. - Original Message - From: Dan Eskildsen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 03, 2001 10:30 AM Subject: [PHP] php/mySql SELECT where clause using dates. ***NEWBIE ALERT*** (I am only very new at this) PHP and MySQL OK - I have a table with data and one of the fields is a Date field. Now I would like to SELECT all records where the date is less that 45 days old. Eh, How do I do that? I have tried this: #Declaring variables $today=date(Y-m-d); $lastmonth = $today-30 $query = SELECT * FROM customers WHERE Site='egebjergnet' AND DatoOprettet'$lastmonth' ORDER BY FirmaNavn; AND I get an error, of course. hmmm -- == Regards from Denmark, Europe Please cc your reply to [EMAIL PROTECTED] == I haven't lost my mind, I've got it backed up on TAPE somewhere... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] php/mySql SELECT where clause using dates.
Hello, maybe this could help, WHERE TO_DAYS( NOW()) - TO_DAYS( your_date_field ) = 45 py At 04:30 PM 5/3/01 +0200, you wrote: ***NEWBIE ALERT*** (I am only very new at this) PHP and MySQL OK - I have a table with data and one of the fields is a Date field. Now I would like to SELECT all records where the date is less that 45 days old. Eh, How do I do that? I have tried this: #Declaring variables $today=date(Y-m-d); $lastmonth = $today-30 $query = SELECT * FROM customers WHERE Site='egebjergnet' AND DatoOprettet'$lastmonth' ORDER BY FirmaNavn; AND I get an error, of course. hmmm -- == Regards from Denmark, Europe Please cc your reply to [EMAIL PROTECTED] == I haven't lost my mind, I've got it backed up on TAPE somewhere... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] + == + Pierre-Yves Lem@ire + E-MedHosting.com + (514) 729-8100 + [EMAIL PROTECTED] + == -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] MySQL select rand() with weighting?
Hi Guys, Does anyone know how to select one field randomnly from a MySQL table against a weighting column in the same row the field is selected from? For example, SELECT id, url, image FROM table ORDER BY rand() LIMIT 1. And have a column like weighting where values between 1 and 100 are held - The larger values making it more likely that they're going to get pulled out. So say I have three rows in the table, one weighing 100, the others weighing 50, the one weighhing 100 stands a better chance of being selected randomnly? I know I've not explained this too well, but hopefully *someone* will know what I'm rambling on about ;) Thanks as always, James. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] MySQL select rand() with weighting?
On Thu, 3 May 2001 20:15:26 +0100, James, Yz ([EMAIL PROTECTED]) wrote: Hi Guys, Does anyone know how to select one field randomnly from a MySQL table against a weighting column in the same row the field is selected from? For example, SELECT id, url, image FROM table ORDER BY rand() LIMIT 1. i guess you'd just go ' order by rand()*weighting' - Mark And have a column like weighting where values between 1 and 100 are held - The larger values making it more likely that they're going to get pulled out. So say I have three rows in the table, one weighing 100, the others weighing 50, the one weighhing 100 stands a better chance of being selected randomnly? I know I've not explained this too well, but hopefully *someone* will know what I'm rambling on about ;) Thanks as always, James. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: php-list- [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] mySQL - select column
Hi all, Given that the resultset from a mysql query is a 2-d array (of sorts - I don't know), if I retrieve only a single column, is there an easy way to grab it as a linear array? For example - old way: $uids=array(); $res = mysql_db_query("db", 'select uid from tbl;', $link); while ($row = mysql_fetch_row($res)) { array_push ($uids, $row[0]); } It seems a little redundant to make another array if one already exists. The end idea is to get to a list of uids ($uid_str = join(',',$uids)) that can be used in a second query to the db (to make up for the lack of subqueries in mysql). I guess what I'm asking - can you do a join on the mysql resultset array somehow. I know in the perl DBI you can get a column of results, but can't find it in php. Thx. J -- Julian Wood Learning Technologies and Digital Media University of Calgary -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] MySQL SELECT performance suggestions
You don't need to select all the data to get the number of rows. Try this: select count(*) as total from products where $product_query; Then use total instead of num_rows. Be sure the elements in your where clause are properly indexed. [EMAIL PROTECTED] wrote: Could someone suggest some options here for performance improvement? I have a table with about 300,000 entries/records by 7 columns/fields. All of the information varies from entry, but only one field can be declared as UNIQUE. I have PHP perform SELECT queries on the table, and I need to display the results in a format of: "viewing $top to ($top+$depth) of $numrows found" I haven't found a way to determine $numrows without performing a second SELECT in this format: $numrows = mysql_num_rows (db_query("SELECT * FROM products WHERE $product_query")); if ( $numrows $depth ) { $depth = $numrows; } $qid = db_query("SELECT * FROM products WHERE $product_query LIMIT $top, $depth"); So, this takes about twice as long as it would without figuring the $numrows. Does anyone have any suggestions as far as what can be done to speed this up? Assume that the table in question is optimized with indexing and UNIQUE values. Thanks. Lee Howard -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] MySQL SELECT performance suggestions
On Wednesday 10 January 2001 20:58, [EMAIL PROTECTED] wrote: I have PHP perform SELECT queries on the table, and I need to display the results in a format of: "viewing $top to ($top+$depth) of $numrows found" I haven't found a way to determine $numrows without performing a second SELECT in this format: look for mysql_num_rows () -- Christian Reiniger LGDC Webmaster (http://sunsite.dk/lgdc/) Drink wet cement. Get stoned. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]