[PHP] SQL select
heloo, i have got a MySQL table: id_comp id_goods type cooler aa1256 n cooler aa1255 n cooler aa1317 f cooler aa1196 n cpu cpu183 n cpu cpu177 f cpu cpu190 n gk gk1265 f gk gk1258 n gk NULL i gk gk1193 n gk gk1257 n mb mb f ram mem713 n ram me1112 f ram mem704 n ram me1081 n and I need only first row of each component order by type. so: cooler aa1317 f cpu cpu177 f gk NULL i mb mb f ram me1112 f has somebody some idea hot to select? ji nmec, ICQ: 114651500 www.menea.cz - www strnky a aplikace -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL select
Hello, This is a reply to an e-mail that you wrote on Mon, 21 Jul 2003 at 11:08, lines prefixed by '' were originally written by you. heloo, i have got a MySQL table: id_comp id_goods type has somebody some idea hot to select? This is off topic for this list but here goes... SELECT * FROM tablename GROUP BY id_comp will probably do it. David. -- phpmachine :: The quick and easy to use service providing you with professionally developed PHP scripts :: http://www.phpmachine.com/ Professional Web Development by David Nicholson http://www.djnicholson.com/ QuizSender.com - How well do your friends actually know you? http://www.quizsender.com/ (developed entirely in PHP) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL select
* Thus wrote Jiøí Nìmec ([EMAIL PROTECTED]): and I need only first row of each component order by type. so: cooler aa1317 f cpu cpu177 f gk NULL i mb mb f ram me1112 f has somebody some idea hot to select? Suggestion: invest in a SQL book. Curt -- I used to think I was indecisive, but now I'm not so sure. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] SQL select
hello, i have got a problem with SQL select: I have got a table such this: id_k typ name id 1 f bla1 1 2 f bla2 1 2 i bla3 1 3 z bla4 1 3 f bla5 1 4 i bla6 1 4 z bla7 1 5 z bla8 1 and id = 1 and I need select these rows: id_k typ nazev id 1 f bla1 1 2 f bla2 1 3 f bla5 1 4 i bla6 1 so, when doesn'i exist component (id_k = component) type f so I want component with type i, but when doesn't exist type f noir i I don't want to select row with type z. jiri nemec, ICQ: 114651500 www.menea.cz - www strnky a aplikace -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL select
I did not understand you much but would not this work? SELECT * FROM table WHERE typ='f' OR typ='i' Ji Nmec wrote: hello, i have got a problem with SQL select: I have got a table such this: id_k typ name id 1 f bla1 1 2 f bla2 1 2 i bla3 1 3 z bla4 1 3 f bla5 1 4 i bla6 1 4 z bla7 1 5 z bla8 1 and id = 1 and I need select these rows: id_k typ nazev id 1 f bla1 1 2 f bla2 1 3 f bla5 1 4 i bla6 1 so, when doesn'i exist component (id_k = component) type f so I want component with type i, but when doesn't exist type f noir i I don't want to select row with type z. jiri nemec, ICQ: 114651500 www.menea.cz - www strnky a aplikace -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL select
Jiøí Nìmec wrote: hello, i have got a problem with SQL select: I have got a table such this: id_k typ name id 1 f bla1 1 2 f bla2 1 2 i bla3 1 3 z bla4 1 3 f bla5 1 4 i bla6 1 4 z bla7 1 5 z bla8 1 and id = 1 and I need select these rows: id_k typ nazev id 1 f bla1 1 2 f bla2 1 3 f bla5 1 4 i bla6 1 so, when doesn'i exist component (id_k = component) type f so I want component with type i, but when doesn't exist type f noir i I don't want to select row with type z. I don't think you can do what you want in a simple SQL statement. You seem to want to return rows based on what is in other rows of the same table. There may be ways to join the table to itself, but I'm not aware of a straightforward way to do this only in SQL. In PHP, it's probably not too tough. Just to be sure we all understand, I assume you want the following: -At most, one row for each id_k -If there is a row with an 'f' in the type column, return that row. -If there is no record with an 'f' in the type column for that id_k, and there is a record with an 'i', return that row. -If there is no record with an 'f', nor a record with an 'i' in the type column, do not return a row. Is that right? jiri nemec, ICQ: 114651500 www.menea.cz - www stránky a aplikace -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php = Mark Weinstock [EMAIL PROTECTED] *** You can't demand something as a right unless you are willing to fight to death to defend everyone else's right to the same thing. *** __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL select
Hmm. How about: SELECT DISTINCT id_k, name FROM tablename WHERE typ != 'z' ORDER BY typ ASC; Does that work? On Thu, 2003-07-10 at 11:29, Mark wrote: Ji Nmec wrote: hello, i have got a problem with SQL select: I have got a table such this: id_k typ name id 1 f bla1 1 2 f bla2 1 2 i bla3 1 3 z bla4 1 3 f bla5 1 4 i bla6 1 4 z bla7 1 5 z bla8 1 and id = 1 and I need select these rows: id_k typ nazev id 1 f bla1 1 2 f bla2 1 3 f bla5 1 4 i bla6 1 so, when doesn'i exist component (id_k = component) type f so I want component with type i, but when doesn't exist type f noir i I don't want to select row with type z. I don't think you can do what you want in a simple SQL statement. You seem to want to return rows based on what is in other rows of the same table. There may be ways to join the table to itself, but I'm not aware of a straightforward way to do this only in SQL. In PHP, it's probably not too tough. Just to be sure we all understand, I assume you want the following: -At most, one row for each id_k -If there is a row with an 'f' in the type column, return that row. -If there is no record with an 'f' in the type column for that id_k, and there is a record with an 'i', return that row. -If there is no record with an 'f', nor a record with an 'i' in the type column, do not return a row. Is that right? jiri nemec, ICQ: 114651500 www.menea.cz - www strnky a aplikace -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php = Mark Weinstock [EMAIL PROTECTED] *** You can't demand something as a right unless you are willing to fight to death to defend everyone else's right to the same thing. *** __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Adam Voigt ([EMAIL PROTECTED]) Linux/Unix Network Administrator The Cryptocomm Group -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] SQL Select on a DATE field
Hi all!
I would like to ask your advice on a script that I wrote. As you can surely
see from my script below, I really need help as I am totally new to PHP.
Regular expressions are my worst nightmare because I don't have a
background in software programming: I'm just kidding around here! Please
post your suggestions. Thanks.
Problem
---
HTML form to filter records from an SQL database on a DATE field.
Search expressions are of the form:
DATE (should be transformed into = DATE)
= DATE (or other operator)
BETWEEN DATE_1 AND DATE_2
The variable named $order_date contains the search expression submitted by
the HTML form.
Here's my script
// Regex pattern for date, e.g. DD/MM/YY or MM-DD-
$date_pat = '[0-9]+(\/|-)[0-9]+(\/|-)[0-9]+';
// Regex pattern for SQL operators
$op_pat = '|=|=|!=|||=';
// First, see if there is at least one date in $_POST['order_date']
$count = preg_match_all('/'.$date_pat.'/', $order_date, $matches);
if ($count) {
for ($i = 0; $i = $count - 1; $i++) {
// Quote dates using PEAR::DB
$quoted_date[$i] = $db-quote($matches[0][$i]);
}
}
// Then see if search string is of type BETWEEN DATE_1 AND DATE_2
$count = preg_match_all('/between\s'.$date_pat.'\sand\s'.$date_pat.'/i',
$order_date, $matches);
if ($count) {
// Yes it's a BETWEEN...AND. We assume that we have two valid dates
$date_filter = ' BETWEEN '.$quoted_date[0].' AND '.$quoted_date[1];
} else {
// No. Then check if we have a search string like '= DATE'
$count = preg_match_all('/'.$op_pat.'/', $order_date, $matches);
if ($count) {
// Yes, then use operator and quoted date
$date_filter = ' '.$matches[0][0].' '.$quoted_date[0];
} else {
// No, then we just use something like ' = DATE'
$date_filter = ' = '.$quoted_date[0];
}
}
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Select Unique() ?
On Thu, 19 Apr 2001, James, Yz wrote: Is there a method of extracting rows from a MySQL table Uniquely (as in only once) ?. For example, when a user performs a search, using two words, it may return the same row twice if the search is spread over two or more SQL selections. An example: I am not sure whether you can do it on mysql. On postgresql you can do a select distinct ... Anuradha -- 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] SQL Select Unique() ?
In article Pine.LNX.4.21.0105021106340.342-10@presario, [EMAIL PROTECTED] (Anuradha Ratnaweera) wrote: Is there a method of extracting rows from a MySQL table Uniquely (as in only once) ?. For example, when a user performs a search, using two words, it may return the same row twice if the search is spread over two or more SQL selections. An example: I am not sure whether you can do it on mysql. On postgresql you can do a select distinct ... The distinct keyword also works in mysql (the manual at mysql.com provides details). -- CC -- 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] SQL Select Unique() ?
Hi Guys, Is there a method of extracting rows from a MySQL table Uniquely (as in only once) ?. For example, when a user performs a search, using two words, it may return the same row twice if the search is spread over two or more SQL "selections". An example: If hypothetical row 129 is a Public House, but the public house doubles up as a restaurant, a search like this might return the same result twice. Here is our hypothetical pub: id = 129 name = The Blue Bell Inn category = Public House description = The Blue Bell Inn is a hypothetical pub, in the heart of rural England. Why not visit, have a drink and perhaps even dine in our fine Restaurant area. The user might perform the search, searching by "category" OR by "description". So if they typed "Public House / Restaurant" as the query, the following would occur: $sql = "SELECT * FROM table WHERE category LIKE 'Public House / Restaurant' OR description LIKE 'Public House / Restaurant'"; Surely that would bring the same row back twice. Is there any way of selecting from the table just once, without having to restrict the search facility to something like: "SELECT * FROM table WHERE category LIKE '%$searchtext%'"; as opposed to having the "OR" in as well? 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] SQL Select Unique() ?
On Fri, 20 Apr 2001 05:41, James, Yz wrote: Hi Guys, Is there a method of extracting rows from a MySQL table Uniquely (as in only once) ?. For example, when a user performs a search, using two words, it may return the same row twice if the search is spread over two or more SQL "selections". An example: If hypothetical row 129 is a Public House, but the public house doubles up as a restaurant, a search like this might return the same result twice. Here is our hypothetical pub: id = 129 name = The Blue Bell Inn category = Public House description = The Blue Bell Inn is a hypothetical pub, in the heart of rural England. Why not visit, have a drink and perhaps even dine in our fine Restaurant area. The user might perform the search, searching by "category" OR by "description". So if they typed "Public House / Restaurant" as the query, the following would occur: $sql = "SELECT * FROM table WHERE category LIKE 'Public House / Restaurant' OR description LIKE 'Public House / Restaurant'"; This of course won't select the hypothetical cae in question. Perhaps you might be betterr off using a dropdown list built from the table of categories that you are using as a lookup table (you are, aren't you?) Surely that would bring the same row back twice. Is there any way of selecting from the table just once, without having to restrict the search facility to something like: "SELECT * FROM table WHERE category LIKE '%$searchtext%'"; Again, that won't work in this case - you might want to separate the words in the search string and do an OR search on all the words. as opposed to having the "OR" in as well? Thanks, as always, James. Broadly, in this type of situation the row will only be returned once notwithstanding that it might meet several citeria. -- David Robley| WEBMASTER Mail List Admin RESEARCH CENTRE FOR INJURY STUDIES | http://www.nisu.flinders.edu.au/ AusEinet| http://auseinet.flinders.edu.au/ Flinders University, ADELAIDE, SOUTH AUSTRALIA -- 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] SQL Select Unique() ?
In article 9bng4u$ftc$[EMAIL PROTECTED],
[EMAIL PROTECTED] ("James, Yz") wrote:
$sql = "SELECT * FROM table WHERE category LIKE 'Public House / Restaurant'
OR description LIKE 'Public House / Restaurant'";
Surely that would bring the same row back twice
Surely not. Have you tried it yet? Unless there are duplicate rows in the
table (which should *not* be the case), each row that is matched by that
query should only be returned once per execution of the query.
Is there any way of
selecting from the table just once, without having to restrict the search
facility to something like:
"SELECT * FROM table WHERE category LIKE '%$searchtext%'";
as opposed to having the "OR" in as well?
I'm not sure what you're after. If executing the top example is getting
you unwanted rows, perhaps you could re-post with some sample data and
pointing out which rows are being shown in duplicate. (It might also be a
good idea to re-check your data first if there's any possibility that there
are duplicate rows existing in the db. 'Cuz that's the kind of thing
that's gonna mis-lead you about how SQL queries normally work.)
--
CC
--
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] SQL - Select rand() ?
Hi Guys, If I wanted to retrieve just one field randomly from a MySQL table, would I just use something like: "SELECT RAND(id) FROM table LIMIT 0,1" ? I suppose the best thing for me to do would be to try it, but I am fast losing the will to stay awake ;) 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] SQL - Select rand() ?
Sup, In the newer versions of mySQL, you can get random results like SELECT id FROM table ORDER BY rand() LIMIT 0,1 SL. - Original Message - From: "James, Yz" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 17, 2001 2:55 PM Subject: [PHP] SQL - Select rand() ? Hi Guys, If I wanted to retrieve just one field randomly from a MySQL table, would I just use something like: "SELECT RAND(id) FROM table LIMIT 0,1" ? I suppose the best thing for me to do would be to try it, but I am fast losing the will to stay awake ;) 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] -- 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]
