[PHP-DB] mysql statement
I am looking for some advice on how to achieve something and so far have been unable to do what I am looking to do. Here is the query I am using: mysql SELECT * - FROM `orders` - WHERE `ordernum` LIKE 35132 - OR `price` LIKE 35132 - OR `partnum` LIKE 35132 - OR `vendor` LIKE 35132 - OR `purpose` LIKE 35132 - OR `tracking` LIKE 35132 - OR `contact` LIKE 35132 - AND `group` LIKE 'mac' - ORDER BY `ordernum` - LIMIT 0 , 30; First here is the table structure: mysql describe orders; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(255) | NO | PRI | | auto_increment | | ordernum| int(10) | NO | | || | date| varchar(60) | NO | | || | time| varchar(20) | NO | | || | group | varchar(20) | NO | | || | quantity| int(10) | NO | | || | description | varchar(255) | NO | | || | price | decimal(3,0) | NO | | || | partnum | varchar(40) | NO | | || | vendor | varchar(65) | NO | | || | purpose | varchar(255) | NO | | || | tracking| varchar(120) | NO | | || | contact | varchar(255) | NO | | || | eta | varchar(50) | NO | | || | department | varchar(125) | NO | | || | notes | varchar(255) | NO | | || +-+--+--+-+-++ 16 rows in set (0.00 sec) I am trying to essentially LIMIT all records returned to be limited by the `group` field so I can search for records and limit the rows returned by that one field. Any tips? TIA. jas -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] mysql statement: two tables, two comparisons
I'm looking for a mysql select statement that does the following: - Check if the value of each expert.id on the table experts matches a value expt.id in the table apliexpert. - For those values where this is true check whether the softID value for that row matches the variable $softId. - return the names that are left after those 2 filters So for tables: Expert idName 1Peter 2Paul 3Mary 4Frank Apliexpert exptid softid 1 3 2 5 2 8 3 9 3 8 1. If the softID is 2, the select should return: peter, paul, mary and frank (frank will always be returned no matter what, because he isn't in apliexpert) 2. If the softID is 3, the select should return: paul, mary and frank 3. If the softID is 8, the select should return: peter and frank 4. If the softID is 9, the select should return: peter, paul and frank J __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] mysql statement: two tables, two comparisons
J, Try this (it assumes that the softid you want to search for is in the variable $softid): $sql = SELECT Expert.* FROM Expert LEFT JOIN Apliexpert ON Expert.id = Apliexpert.exptid WHERE Apliexpert.softid = '$softid' ORDER BY Expert.Name; -- Beverly Steiner [EMAIL PROTECTED] -Original Message- From: Jason End [mailto:[EMAIL PROTECTED] Sent: Thursday, March 06, 2003 8:52 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] mysql statement: two tables, two comparisons I'm looking for a mysql select statement that does the following: - Check if the value of each expert.id on the table experts matches a value expt.id in the table apliexpert. - For those values where this is true check whether the softID value for that row matches the variable $softId. - return the names that are left after those 2 filters So for tables: Expert idName 1Peter 2Paul 3Mary 4Frank Apliexpert exptid softid 1 3 2 5 2 8 3 9 3 8 1. If the softID is 2, the select should return: peter, paul, mary and frank (frank will always be returned no matter what, because he isn't in apliexpert) 2. If the softID is 3, the select should return: paul, mary and frank 3. If the softID is 8, the select should return: peter and frank 4. If the softID is 9, the select should return: peter, paul and frank J __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- 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
[PHP-DB] MySql Statement inside a function?
Hi All I have created a function and insde that function it dose a standard select, fetch_arry, while loop. I get a error from mysql saying not a vaild resource but if I take the statemnet out of the function all works fine and dandy. Are there things I should be aware of when trying to include mysql statements inside a function? I have include my code here if it helps. function getnames(){ $gnlistsql = select name from $tbn2; $gnlistres = mysql_query($gnlistsql); while($nlist = mysql_fetch_array($gnlistres)){ $nlistop = option value=\$nlist[name]\$nlist[name]/option; $nlistsel = select name=\getname\$nlistop/select; echo $nlistsel; } } There error is reported back in the while line... Any help or advice is appreciated as always. P.S reason for the fuction is that I will call the select a name list a few times so I thought of making it a function then calling it when needed. Thank You Dave C -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySql Statement inside a function?
i'm certainly no expert, but here are my thoughts: the variable $tbn2 probabyl has no value inside of your function. so the query is really just select name from. i'd suggest you pass the $tbn2 value to the function. also, it is generally a bad idea to have a echo statements in function. rather you should return the results. hope that helps. -rob -Original Message- From: Dave Carrera [mailto:dave;davecarrera.com] Sent: Friday, October 18, 2002 10:52 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] MySql Statement inside a function? Hi All I have created a function and insde that function it dose a standard select, fetch_arry, while loop. I get a error from mysql saying not a vaild resource but if I take the statemnet out of the function all works fine and dandy. Are there things I should be aware of when trying to include mysql statements inside a function? I have include my code here if it helps. function getnames(){ $gnlistsql = select name from $tbn2; $gnlistres = mysql_query($gnlistsql); while($nlist = mysql_fetch_array($gnlistres)){ $nlistop = option value=\$nlist[name]\$nlist[name]/option; $nlistsel = select name=\getname\$nlistop/select; echo $nlistsel; } } There error is reported back in the while line... Any help or advice is appreciated as always. P.S reason for the fuction is that I will call the select a name list a few times so I thought of making it a function then calling it when needed. Thank You Dave C -- 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] MySql Statement inside a function?
Dave, I'd agree with Rob especially about returning the results. Plus it's always a good idea to add error checking to your SQL statement. Try replacing your existing line of code with this to make sure you query is actually returning valid results before trying to operate on them. $gnlistres = mysql_query($gnlistsql) or die(mysql_error()); (I think that syntax is right). I also don't see where you're actually setting up the db connection and selecting a database. Are you doing so within the scope of this script? Finally, within the while loop, I've always used statements like $var = $row[colName]. I noticed you don't have yours quoted. Don't know if that makes a difference. The error you're getting is before those lines anyway, so maybe not. Just my $.03. Inflation. Hope this helps. Rich -Original Message- From: Dave Carrera [mailto:dave;davecarrera.com] Sent: Friday, October 18, 2002 11:52 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] MySql Statement inside a function? Hi All I have created a function and insde that function it dose a standard select, fetch_arry, while loop. I get a error from mysql saying not a vaild resource but if I take the statemnet out of the function all works fine and dandy. Are there things I should be aware of when trying to include mysql statements inside a function? I have include my code here if it helps. function getnames(){ $gnlistsql = select name from $tbn2; $gnlistres = mysql_query($gnlistsql); while($nlist = mysql_fetch_array($gnlistres)){ $nlistop = option value=\$nlist[name]\$nlist[name]/option; $nlistsel = select name=\getname\$nlistop/select; echo $nlistsel; } } There error is reported back in the while line... Any help or advice is appreciated as always. P.S reason for the fuction is that I will call the select a name list a few times so I thought of making it a function then calling it when needed. Thank You Dave C -- 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