[PHP-DB] Re: Select on Group
On 17/11/15 01:02, Karl DeSaulniers wrote:
Hello All,
Hoping someone can help me with this query.
I want to select some custom fields from my database that are part of a group
of custom fields.
There are several of these groups. I want to (in one sql statement) grab these
fields, all of them
and have them grouped in the results like so.
$custom_fields = array(
'Group1' => array(
'field_ID' => '1',
'field_name' => 'myAddressField',
'filed_slug' => 'my-address-field'
),
'Group2' => array(
'field_ID' => '2',
'field_name' => 'myCityField',
'filed_slug' => 'my-city-field'
),
'Group3' => array(
'field_ID' => '3',
'field_name' => 'myStateField',
'filed_slug' => 'my-state-field'
)
)
Here is the clincher... not all the info is in the same table.
This is what I am doing currently and it works, however I would like to
eliminate calling the database in a foreach loop as well as multiple times to
get my results.
[code]
$FieldGroups = $wpdb->get_results("SELECT DISTINCT Field_Group FROM
".table_name1."");
foreach($FieldGroups as $i=>$FieldGroup) {
$field_group = stripslashes_deep($FieldGroup->Field_Group);
$SQL = "SELECT ft.*, mt.*
FROM ". table_name1." ft
LEFT JOIN ". table_name2." mt
ON mt.Field_ID = ft.Field_ID
WHERE ft.Field_Group='%s' AND mt.Page_ID=%d AND
ft.Field_Display='%s'
ORDER BY ft.Field_ID ASC";
$Fields = $wpdb->get_results($wpdb->prepare($SQL, $field_group,
$Page_ID, $display));
}
[end code]
How can I combine these into one query that fills the result array the way
described above?
I thought of a SELECT inside a SELECT, but my php foo is a little under trained
at the moment.
Not sure how to do such and achieve the results I am after. Any help would be
appreciated.
TIA
Best,
Karl DeSaulniers
Design Drumm
http://designdrumm.com
Hi Karl,
is this all the code ?
Because, if the FOREACH() loop is running over *all* Field_Group fields,
there is nothing to filter - might as well just run the entire INNER
JOIN on table_name1.
In SQLite, there's an IN clause - maybe works in your database :
SELECT * from TABLE_1
WHERE Id IN ( SELECT Id from OTHER_TABLE)
Same thing can be achieved through an INNER JOIN in a more generic way :
SELECT * from TABLE_1 INNER JOIN
(SELECT * from OTHER_TABLE WHERE Id = 'criterion') AS Q1
ON TABLE_1.Id = Q1.Id
Q1, the inline query, limits the field groups to the ones requested by you.
Because it's an INNER JOIN, the join will only return matches with the
select field groups.
Hope I'm making sense ;-)
B.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: SELECT
On Mon, Oct 17, 2011 at 12:58 PM, Jim Giner jim.gi...@albanyhandball.com wrote: I would do it this way: Where $sel_d = (the day # you want) $sel_m = (the month # you want) The where clause would be: Where (start_month = $sel_m and start_day = $sel_d) and (end_month = $sel_m and end_day = $sel_d) Hmm, no this won't work. Check this: start_month=10 start_day=15 end_month=1 end_day=1 sel_day=23 sel_month=12 in this case, sel_month IS greater than start_month and sel_day IS greater than start_day, HOWEVER sel_month is ALSO greater than end_month and sel_day is ALSO greater than end_day in this case. (wrap around to new year problem, wrap around to new month problem) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: SELECT
I would do it this way: Where $sel_d = (the day # you want) $sel_m = (the month # you want) The where clause would be: Where (start_month = $sel_m and start_day = $sel_d) and (end_month = $sel_m and end_day = $sel_d) Someone else will probably have something more elegant, but I believe this will work for you. Never used the between clause before - have to remember that. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: SELECT
Though the operators are = and =, not = and =. Toby -Original Message- From: Jim Giner [mailto:jim.gi...@albanyhandball.com] Sent: Monday, October 17, 2011 1:58 PM To: php-db@lists.php.net Subject: [PHP-DB] Re: SELECT I would do it this way: Where $sel_d = (the day # you want) $sel_m = (the month # you want) The where clause would be: Where (start_month = $sel_m and start_day = $sel_d) and (end_month = $sel_m and end_day = $sel_d) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE: SELECT WHERE length of content question
You should be able to use the MySQL command LENGTH() command. I'm assuming that your fax field is a varchar or char(10) field. If it's a number (which it probably shouldn't be), I suppose you could write a query to find values equal to or greater than 100,000,000,000. http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_length I hope this helps. Geoff -- Forwarded message -- From: Ron Piggott ron.pigg...@actsministries.org To: php-db@lists.php.net Date: Wed, 9 Mar 2011 17:49:42 -0500 Subject: SELECT WHERE length of content question Is there a command in mySQL that would allow me to SELECT the rows where the `fax` column is more than 11 characters long? OR Do I need to use PHP to assess this? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info http://www.theverseoftheday.info/
Re: [PHP-DB] RE: SELECT WHERE length of content question
Do not forget to use TRIM function as well: LENGTH(TRIM(fax)) in order to elliminate leading and trailing spaces. Aurel - Original Message - From: Geoffrey Pitman geoffrey.pit...@gmail.com To: php-db@lists.php.net Sent: Friday, March 11, 2011 7:59 PM Subject: [PHP-DB] RE: SELECT WHERE length of content question You should be able to use the MySQL command LENGTH() command. I'm assuming that your fax field is a varchar or char(10) field. If it's a number (which it probably shouldn't be), I suppose you could write a query to find values equal to or greater than 100,000,000,000. http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_length I hope this helps. Geoff -- Forwarded message -- From: Ron Piggott ron.pigg...@actsministries.org To: php-db@lists.php.net Date: Wed, 9 Mar 2011 17:49:42 -0500 Subject: SELECT WHERE length of content question Is there a command in mySQL that would allow me to SELECT the rows where the `fax` column is more than 11 characters long? OR Do I need to use PHP to assess this? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info http://www.theverseoftheday.info/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: SELECT query
One more thing ... Bible is stored by verses, t is the text of the verse On Sun, 2008-12-21 at 19:52 -0500, Ron Piggott wrote: I am working on a web based Bible searching query. So far I am able to generate: SELECT * FROM `bible_verses` INNER JOIN `bible_books` ON `bible_books`.`id` = `bible_verses`.`b` WHERE `t` IN ( 'Lord' , 'Jesus' ) LIMIT 0 , 10 Is an IN the correct syntax to use? I am trying to take what the user types in (variable is $keyword_search) and allow a search where if the same 2 or 3 words are in the verse of the Bible that verse would be a match, but not necessarily be beside each other. $keyword_search_string = str_replace( , ' , ', $keyword_search); $query .= IN ( ' . $keyword_search_string . ' ) ; How do I do this correctly? Ron -- Acts Ministries Christian Evangelism Where People Matter 12 Burton Street Belleville, Ontario, Canada K8P 1E6 ron.pigg...@actsministries.org www.actsministrieschristianevangelism.org In Belleville Phone: (613) 967-0032 In North America Call Toll Free: (866) ACTS-MIN Fax: (613) 967-9963 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: SELECT query
On Sun, Dec 21, 2008 at 7:56 PM, Ron Piggott ron.pigg...@actsministries.org wrote: One more thing ... Bible is stored by verses, t is the text of the verse On Sun, 2008-12-21 at 19:52 -0500, Ron Piggott wrote: I am working on a web based Bible searching query. So far I am able to generate: SELECT * FROM `bible_verses` INNER JOIN `bible_books` ON `bible_books`.`id` = `bible_verses`.`b` WHERE `t` IN ( 'Lord' , 'Jesus' ) LIMIT 0 , 10 Is an IN the correct syntax to use? I am trying to take what the user types in (variable is $keyword_search) and allow a search where if the same 2 or 3 words are in the verse of the Bible that verse would be a match, but not necessarily be beside each other. $keyword_search_string = str_replace( , ' , ', $keyword_search); $query .= IN ( ' . $keyword_search_string . ' ) ; How do I do this correctly? Ron -- Acts Ministries Christian Evangelism Where People Matter 12 Burton Street Belleville, Ontario, Canada K8P 1E6 ron.pigg...@actsministries.org www.actsministrieschristianevangelism.org In Belleville Phone: (613) 967-0032 In North America Call Toll Free: (866) ACTS-MIN Fax: (613) 967-9963 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php query code looks correct. are you getting an error? have you tried echoing out the sql and testing in phpmyadmin or some other gui tool? -- Bastien Cat, the other other white meat
Re: [PHP-DB] Re: SELECT query
Bastien Koert wrote:
On Sun, Dec 21, 2008 at 7:56 PM, Ron Piggott ron.pigg...@actsministries.org
wrote:
One more thing ... Bible is stored by verses, t is the text of the verse
On Sun, 2008-12-21 at 19:52 -0500, Ron Piggott wrote:
I am working on a web based Bible searching query. So far I am able to
generate:
SELECT * FROM `bible_verses` INNER JOIN `bible_books` ON
`bible_books`.`id` = `bible_verses`.`b` WHERE `t` IN ( 'Lord' ,
'Jesus' ) LIMIT 0 , 10
Is an IN the correct syntax to use?
This will work fine. An IN clause is like multiple OR's:
... where t = 'Lord' OR t = 'Jesus' ...
I am trying to take what the user types in (variable is $keyword_search)
and allow a search where if the same 2 or 3 words are in the verse of
the Bible that verse would be a match, but not necessarily be beside
each other.
$keyword_search_string = str_replace( , ' , ', $keyword_search);
$query .= IN ( ' . $keyword_search_string . ' ) ;
This will seem a little long winded but you need to check for sql
injection. As it stands, you'll get an error when you search for a name
with a quote (o'reilly). Might not be in the bible (I have no idea) but
doesn't mean someone won't try it ;)
// look at the keywords one by one - they are space separated
$keywords = explode(' ', $keyword_search);
// now go through them all and escape them.
$keyword_searches = array();
foreach ($keywords as $keyword) {
$keyword_searches[] = mysql_real_escape_string($keyword);
}
// then turn it all into an IN string.
$keyword_search_string = IN (' . implode(',', $keyword_searches) . ');
You may want to eventually look at full text searching, depending on how
slow this is and how important search is to your site. The syntax is
completely different.
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: SELECT
Ron Piggott (PHP wrote: Yesterday I asked how to get the date time 90 minutes ago and I received several responses. Thanks. I don't think this select statement is working correctly. (Correctly being what I am intending it to do) I took a look at the table this morning. One record remains that was created 2006-01-19 at 23:55:37. These are the values of date_created and time_created. The current values are approximately 2006-01-20 and 05:50:00 This is the select statement I am writing about: SELECT * FROM `table` WHERE `date_created` = '$date_90_minutes_ago' AND `time_created` = '$time_90_minutes_ago' Intellectually I know the problem: 05:50:00 is much earlier than 23:55:37 ... thus my AND is not allowing both conditions to exist together. Is there a way that I may modify this SELECT statement so the present conditions continue to exist and add a second part to the SELECT statement that if the time is 01:30:00 or higher that records from the previous day are selected? This continues to allow the 90 minute time frame for users logged into my web site ... I am not sure how you would add an OR function to the above without messing up what presently works :) (I am writing a SESSION function for my web site using mySQL and a cron. The select statement I quoted above is part of the cron.) Ron It would seem you have different columns for date and time? Seems to me that a little judicious use of CONCAT and DATE_SUB might solve your problem. In other words, create a valid date/timestamp value with CONCAT then use DATE_SUB to determine -90 min. I've given a Mysql based solution as this is php.db :-) Cheers -- David Robley I'm an ordained minister, said Tom reverently. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: SELECT
Ron You want SELECT DISTINCT ... or SELECT column list FROM table GROUP BY column list In either case you probably can't use SELECT * - you have to specify the columns you're after. This is because these commands will look for any differences and if they see one they'll give you a new row; if all you're after is the userID use SELECT DISTINCT userID ... this will only return a particular value once. See the documentation for your particular DBMS; GROUP BY is the more powerful with all kinds of aggregate functions available but sounds like SELECT DISTINCT will work fine for what your after. Good Luck, Frank On Dec 10, 2005, at 5:02 PM, [EMAIL PROTECTED] wrote: From: Ron Piggott (PHP) [EMAIL PROTECTED] Date: December 10, 2005 5:04:28 PM PST To: PHP DB php-db@lists.php.net Subject: SELECT Reply-To: [EMAIL PROTECTED] I am trying to put together a SELECT syntax. I am querying a response database and I only want to obtain each user's name once even if they have given more than 1 response. $query=SELECT * FROM conversation_table WHERE conversation_reference = $conversation_currently_displayed; $response_created_by = mysql_result($result,$i,response_created_by); My ideal is that if users 1, 2, 4 5 are in dialogue with each other the above SELECT $query will only give the results of their identity once with the mysql_request() function Thanks for your help. Ron
[PHP-DB] Re: SELECT html table
Ron Piggott (PHP) wrote:
I would like to display the contents of my table with the first row
being grey in background and the next row being white and the third row
being grey, fourth being white, etc. I am not sure how to do this.
Well, that's pretty easy to do actually :P
You have your while() statement to execute your displaying part. In
there, you add some if()'s
I'll use an example for 20 posts. An example:
$array1 = array(1,3,5,7,9,11,13,15,17,19)
?php
while($i $totalrows)
{
$j = $i - 1;
if($i == array1[$j])
{
--some code--
echo tb class=\grey\all the rest/tb;
}
else
{
--some code--
echo tb class=\white\all the rest/tb;
}
--more code--
}
?
This is a really easy way to do it, and I guess it works :)
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: SELECT html table
From: Ron Piggott (PHP) [EMAIL PROTECTED]To: PHP DB php-db@lists.php.netDate: Sun, 04 Dec 2005 13:38:58 -0500Subject: SELECT html table I have two questions. I would like to display the contents of my table with the first row being grey in background and the next row being white and the third row being grey, fourth being white, etc. I am not sure how to do this.Secondly I only want the first 20 records to be displayed at a time and then I want to create a NEXT link for the next 20 records (21-40) ... any idea how you would use the SELECT command to do this?Ron [input] [input] [input] [input] [input] [input] [input] [input] Visit http://www.mysql.com/ and read a little bit about LIMIT command. :) Jerry - Do you Yahoo!? Take your Mail with you - get Yahoo! Mail on your mobile - Do you Yahoo!? Never miss an Instant Message - Yahoo! Messenger for SMS
[Fwd: Re: [PHP-DB] Re: SELECT html table]
--
Best Regards,
Shahmat Dahlan
Research and Development
SAINS
Mobile: +(60)16 882 6130
Office: +(60)82 426 733 ext 5512
---BeginMessage---
(1)
i've been using this method for years now, maybe somebody else has a
better way to do this?
?
sql query
$i = 0;
while (sql result fetches) {
if ($i%2 =! 0) { $bg = 'background=white'; }
else { $bg = ''; }
echo tr $bg;
php codes
$i++;
}
?
(2) check out the limit syntax in the mysql manuals.
select * from table limit startindex, number of rows
you should work out on how to use the concept of pages, and how many
rows in a page, in which you had mentioned 20.
count the number of page base on the number of records returned then you
get the total number of pages for particular returned result
i'm thinking of the function ceil, at least i think it's the ceiling
function.
e.g.
if pg = 1
maxrows=20
start = ($pg-1)*$maxrows
select * from table $start, $maxrows
pls feel free to correct me
JeRRy wrote:
From: Ron Piggott (PHP) [EMAIL PROTECTED]To: PHP DB php-db@lists.php.netDate: Sun, 04 Dec 2005 13:38:58 -0500Subject: SELECT html table
I have two questions. I would like to display the contents of my table with the first row being grey in background and the next row being white and the third row being grey, fourth being white, etc. I am not sure how to do this.Secondly I only want the first 20 records to be displayed at a time and then I want to create a NEXT link for the next 20 records (21-40) ... any idea how you would use the SELECT command to do this?Ron
[input] [input] [input] [input] [input] [input] [input] [input]
Visit http://www.mysql.com/ and read a little bit about LIMIT command. :)
Jerry
-
Do you Yahoo!?
Take your Mail with you - get Yahoo! Mail on your mobile
-
Do you Yahoo!?
Never miss an Instant Message - Yahoo! Messenger for SMS
--
Best Regards,
Shahmat Dahlan
Research and Development
SAINS
Mobile: +(60)16 882 6130
Office: +(60)82 426 733 ext 5512
---End Message---
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: Select
Miguel Guirao wrote: You are right David, I have an auto_increment field!! What about this: $items2 = mysql_query(SELECT DISTINCT rev FROM rev ORDER BY rev, $link); where rev is a revision field for parts in my wharehouse!!! Does it will work in this case? Thanks -Original Message- From: David Robley [mailto:[EMAIL PROTECTED] Sent: Martes, 17 de Mayo de 2005 03:42 p.m. To: php-db@lists.php.net Subject: [PHP-DB] Re: Select Miguel Antonio Guirao Aguilar wrote: Hi!! I have this query in PHP: $items2 = mysql_query(SELECT DISTINCT * FROM rev ORDER BY rev, $link); I have three rows with the same data on it, and DISTINCT seems to be not working, since I got all the rows, any ideas of what is going wrong? Without knowing how many fields you have and what they are, no. But you should realise that DISTINCT looks at _all_ selected fields in a record, so if you have an auto_increment id field in amongst those selected, all the records will be different. David That should work as you expect it to. David -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Select
Miguel Antonio Guirao Aguilar wrote: Hi!! I have this query in PHP: $items2 = mysql_query(SELECT DISTINCT * FROM rev ORDER BY rev, $link); I have three rows with the same data on it, and DISTINCT seems to be not working, since I got all the rows, any ideas of what is going wrong? Without knowing how many fields you have and what they are, no. But you should realise that DISTINCT looks at _all_ selected fields in a record, so if you have an auto_increment id field in amongst those selected, all the records will be different. David -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE: select inside a while loop
Hello,
Your code looks well. But is the variable $db the name of your database or
your link-identifier. When it is the name of your database i'm not really
surpised your code wouldn't work. mysql_query requires as second argument a
link identifier.
Rolf van de Krol
-Oorspronkelijk bericht-
Van: Mike Baerwolf [mailto:[EMAIL PROTECTED]
Verzonden: vrijdag 28 november 2003 6:06
Aan: [EMAIL PROTECTED]
Onderwerp: select inside a while loop
Hello,
I have two mysql tables songs and artists. They look like this:
CREATE TABLE `artists` (
`artist_id` int(10) unsigned NOT NULL auto_increment,
`artist_name` varchar(100) default NULL,
`artist_img` varchar(50) default NULL,
PRIMARY KEY (`artist_id`),
UNIQUE KEY `artist_name` (`artist_name`),
KEY `artist_id` (`artist_id`)
) TYPE=MyISAM;
CREATE TABLE `songs` (
`song_id` int(11) NOT NULL auto_increment,
`song_title` tinytext,
`artist_id` tinytext,
PRIMARY KEY (`song_id`)
) TYPE=MyISAM;
Currently I have the artist_id in the songs table setup has a text field
with artist names in them temporarily. First I want to select all the
artist_ids(with the names) and find the artist_id for that name in the
artist table. Then update the artist_id in the song table with the
artist_id in the artist table. Then convert the artist_id in the song
table to int.
So with all that said here is what i have done that doesn't work,
$result = mysql_query(SELECT artist_id FROM songs,$db) or
die(mysql_error());
if ($row = mysql_fetch_row($result)){
do {
$artist_name = $row[artist_id];
$result_1 = mysql_query(SELECT artist_id,artist_name FROM
artists WHERE artist_name = '$artist_name',$db);
$row_1 = mysql_fetch_array($result_1);
print $row_1[artist_id]-$row_1[artist_name];
}while ($row = mysql_fetch_array($result));
}
I haven't even been able to get to the update part. I'm pretty sure the
above fails because of the var $artist_name after the first run through.
Any help would be appreciated.
Thanks,
Mike
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] RE: select inside a while loop
Sorry, just say the message.
Your code looks well. But is the variable $db the name of your database or
your link-identifier. When it is the name of your database i'm not really
surpised your code wouldn't work. mysql_query requires as second argument a
link identifier.
Read what Rolf says. Very wise words here. :-)
So with all that said here is what i have done that doesn't work,
$result = mysql_query(SELECT artist_id FROM songs,$db) or
die(mysql_error());
if ($row = mysql_fetch_row($result)){
do {
$artist_name = $row[artist_id];
$result_1 = mysql_query(SELECT artist_id,artist_name FROM
artists WHERE artist_name = '$artist_name',$db);
$row_1 = mysql_fetch_array($result_1);
Why don't you make one query with a union between the 2 tables and then pass
through the results with a loop and maybe an if inside it? Ypou are making to
many connections to the DB.
P.D.: Any way, your problem will be solved with Rolfs advice.
--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-
Martín Marqués |[EMAIL PROTECTED]
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: SELECT FROM 2 or more tables
ive heard that before, and its in the mysql manual, to have something like WHERE table1.id = table2.id But does make any sense to me. What Im doing is providing a search form for customers that allows them to search each individual table for content or all tables. all tables have a common field, keywords, which i have searched. maybe im stupid, but i cant c where WHERE table1.id = table2.id would help, or sumting along that lines John Ryan [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Is it easy?? Is it possible?? Should I just run 2 different queries and output their results in the PHP script and make it look like it's all from the same table I cant grasp JOIN for the life of me -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: select from multi tables
SELECT SUM(T1.F1) AS C1, SUM(T2.F1) AS C2 FROM T1, T2 Hi, T1,T2 is a join of the two tables which means that you combine each value of T1 with each value of T2. This is why you don't get the result you want. In your case I simply would do two querys as it won't make any problems: SELECT SUM T1.F1 FROM T1; SELECT SUM T2.F2 FROM T2; Regards, Bastian -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: select from multi tables
thanks, i think your probably right. Another problem I have is trying to sum the totals of F1 over muliple tables, what I would like is to be able to use the UNION statement but my site is hosted and they are not using version 4x of MySQL. Is there any work around for this using a single SQL statement or again will I need to use multiple statments and add up the results with php? thanks again -Original Message- From: Bastian Vogt [mailto:[EMAIL PROTECTED]] Sent: 03 February 2003 13:12 To: [EMAIL PROTECTED] Subject: [PHP-DB] Re: select from multi tables SELECT SUM(T1.F1) AS C1, SUM(T2.F1) AS C2 FROM T1, T2 Hi, T1,T2 is a join of the two tables which means that you combine each value of T1 with each value of T2. This is why you don't get the result you want. In your case I simply would do two querys as it won't make any problems: SELECT SUM T1.F1 FROM T1; SELECT SUM T2.F2 FROM T2; Regards, Bastian -- 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] Re: SELECT problem
try put the following line right after you call mysql_query(): echo mysql_error(); see what's the error. Foong Sabina A. Schneider [EMAIL PROTECTED] wrote in message 001001c2bde8$de3c95d0$489346c8@mansa">news:001001c2bde8$de3c95d0$489346c8@mansa... Hello PHP world!!! i've just finisched installing Apache 1.3.27 with PHP4 and 1.4 in Windows 2000. I've installed all off the scripts that function all right in Linux Red Hat in Windows 2000, but when it comes to do a simple select to a table that does exist and the select is all right there appears a message as if the query weren't all right at all: Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in g:\apache\apache\htdocs\digimedia\adm_central\index.php on line 18 What could it be? I really appreciate the great help you have been giving to me these days. Thank you very much!!! Sabina Alejandra Schneider [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: select date YYYY-MM mysql help
Thanks everyone for their help! Peter Goggin [EMAIL PROTECTED] wrote in message 008f01c2b32a$b50b2500$0301a8c0@petergoggin">news:008f01c2b32a$b50b2500$0301a8c0@petergoggin... If mysql has a between function then you could try: where date between '2002-12-01'and '2002-12-31'. Otherwise try where date '2002-12-01 00:00:00'and date '2002-12-31 23:59:59' Regards Peter Goggin - Original Message - From: Matthew Moldvan [EMAIL PROTECTED] To: 'Max Clark' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, January 03, 2003 9:38 AM Subject: RE: [PHP-DB] select date -MM mysql help Sounds more like an SQL question than PHP, but try the following: SELECT * FROM table WHERE date LIKE '2002-12%'; Regards, Matthew Moldvan --- System Administrator Trilogy International, Inc http://www.trilogyintl.com/ecommerce/ --- -Original Message- From: Max Clark [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 5:40 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] select date -MM mysql help Hi all, I would like to do a query like this from mysql: select * from table where date = '2002-12'; However unless I have the complete date '2002-12-26' the query doesn't work correctly. What else do I need? Thanks in advance, Max -- 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 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] Re: select date YYYY-MM mysql help
Which solution did you end up using? Just curious, I wasn't sure if mine was correct ... Regards, Matthew Moldvan --- System Administrator Trilogy International, Inc http://www.trilogyintl.com/ecommerce/ --- -Original Message- From: Max Clark [mailto:[EMAIL PROTECTED]] Sent: Friday, January 03, 2003 3:02 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Re: select date -MM mysql help Thanks everyone for their help! Peter Goggin [EMAIL PROTECTED] wrote in message 008f01c2b32a$b50b2500$0301a8c0@petergoggin">news:008f01c2b32a$b50b2500$0301a8c0@petergoggin... If mysql has a between function then you could try: where date between '2002-12-01'and '2002-12-31'. Otherwise try where date '2002-12-01 00:00:00'and date '2002-12-31 23:59:59' Regards Peter Goggin - Original Message - From: Matthew Moldvan [EMAIL PROTECTED] To: 'Max Clark' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, January 03, 2003 9:38 AM Subject: RE: [PHP-DB] select date -MM mysql help Sounds more like an SQL question than PHP, but try the following: SELECT * FROM table WHERE date LIKE '2002-12%'; Regards, Matthew Moldvan --- System Administrator Trilogy International, Inc http://www.trilogyintl.com/ecommerce/ --- -Original Message- From: Max Clark [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 5:40 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] select date -MM mysql help Hi all, I would like to do a query like this from mysql: select * from table where date = '2002-12'; However unless I have the complete date '2002-12-26' the query doesn't work correctly. What else do I need? Thanks in advance, Max -- 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 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 Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: select date YYYY-MM mysql help
I guess for consistencies sake you can use SELECT * FROM table WHERE date LIKE '2003-01%' also ... just a thought. :-) Anyway, glad to help out. Regards, Matthew Moldvan --- System Administrator Trilogy International, Inc http://www.trilogyintl.com/ecommerce/ --- -Original Message- From: Max Clark [mailto:[EMAIL PROTECTED]] Sent: Friday, January 03, 2003 3:35 PM To: 'Matthew Moldvan'; [EMAIL PROTECTED] Subject: RE: [PHP-DB] Re: select date -MM mysql help I ended up using: SELECT * FROM table WHERE date LIKE '2002-12%'; And: SELECT * FROM table_name WHERE date BETWEEN '2003-01-01' AND '2003-01-31'; Thanks again for the help! -Max -Original Message- From: Matthew Moldvan [mailto:[EMAIL PROTECTED]] Sent: Friday, January 03, 2003 12:16 PM To: 'Max Clark'; [EMAIL PROTECTED] Subject: RE: [PHP-DB] Re: select date -MM mysql help Which solution did you end up using? Just curious, I wasn't sure if mine was correct ... Regards, Matthew Moldvan --- System Administrator Trilogy International, Inc http://www.trilogyintl.com/ecommerce/ --- -Original Message- From: Max Clark [mailto:[EMAIL PROTECTED]] Sent: Friday, January 03, 2003 3:02 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Re: select date -MM mysql help Thanks everyone for their help! Peter Goggin [EMAIL PROTECTED] wrote in message 008f01c2b32a$b50b2500$0301a8c0@petergoggin">news:008f01c2b32a$b50b2500$0301a8c0@petergoggin... If mysql has a between function then you could try: where date between '2002-12-01'and '2002-12-31'. Otherwise try where date '2002-12-01 00:00:00'and date '2002-12-31 23:59:59' Regards Peter Goggin - Original Message - From: Matthew Moldvan [EMAIL PROTECTED] To: 'Max Clark' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, January 03, 2003 9:38 AM Subject: RE: [PHP-DB] select date -MM mysql help Sounds more like an SQL question than PHP, but try the following: SELECT * FROM table WHERE date LIKE '2002-12%'; Regards, Matthew Moldvan --- System Administrator Trilogy International, Inc http://www.trilogyintl.com/ecommerce/ --- -Original Message- From: Max Clark [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 5:40 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] select date -MM mysql help Hi all, I would like to do a query like this from mysql: select * from table where date = '2002-12'; However unless I have the complete date '2002-12-26' the query doesn't work correctly. What else do I need? Thanks in advance, Max -- 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 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 Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: select only where 2 things are true
SELECT jouw_id FROM A AS A1, A AS A2 WHERE A1.merkid=1 AND A2.merk_id=2 AND A1.jouw_id=A2.jouw_id this is a self join! thanks to vincent @ www.yapf.net -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: select distinct with all columns
Perfect! Thanks. I'm still trying to get a handle on the GROUP BY command. It's still a little bit like magic to me. And in the manual, they don't really show how to use it like the way I wanted to use it. Thanks again, russ On Monday 12 August 2002 01:27 pm, Dan Koken wrote: SELECT * FROM equipement WHERE class = microphones GROUP BY type; Russ Arbuthnot wrote: I have a mysql table named equipment with 11 columns named: id, staff_member, class, type, manufacturer, model, description, picture, created, modified, and published. I'm trying to write a select statement similar to this: SELECT DISTINCT type FROM equipement WHERE class = microphones; yet shows all 11 columns of the selected rows rather than just the type column. The only way I know how to show all the columns is to use SELECT *, or to list all the columns manually like SELECT id, type, class, ... etc. But when I tried doing this: SELECT DISTINCT type, id, staff_member, class, manufacturer, model, description, picture, created, modified, published FROM equipment WHERE class = microphones; I just got the exact same answer as if i would have done this: SELECT * FROM equipment WHERE class = microphones; so it didn't help. Can anyone offer a hint? Thanks, russ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: select distinct with all columns
SELECT * FROM equipement WHERE class = microphones GROUP BY type; Russ Arbuthnot wrote: I have a mysql table named equipment with 11 columns named: id, staff_member, class, type, manufacturer, model, description, picture, created, modified, and published. I'm trying to write a select statement similar to this: SELECT DISTINCT type FROM equipement WHERE class = microphones; yet shows all 11 columns of the selected rows rather than just the type column. The only way I know how to show all the columns is to use SELECT *, or to list all the columns manually like SELECT id, type, class, ... etc. But when I tried doing this: SELECT DISTINCT type, id, staff_member, class, manufacturer, model, description, picture, created, modified, published FROM equipment WHERE class = microphones; I just got the exact same answer as if i would have done this: SELECT * FROM equipment WHERE class = microphones; so it didn't help. Can anyone offer a hint? Thanks, russ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Select * from t1.id where not equal to t2.id ???
Dave, Try something like this: SELECT * FROM t1 LEFT JOIN t2 ON t2.id = t1.id WHERE t2.id is NULL HTH. Dan. Dave Carrera wrote: High all I cant seem to get this sql working. I am trying to pull everything from table 1 (t1) where t1.id is not equal to table 2 (t2) id I cant find examples anywhere. Any help or guidance with this as always is very much appreciated. Thanks in Advance Dave C -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Select string.
Single quotes within the select work fine. $login = mysql_query(SELECT * FROM Users WHERE Username = '$User_Name'); On Thu, 11 Jul 2002 06:47:54 +1200, Shiloh Madsen wrote: Yet another thing i need some quick help with. What is the proper way to generate a select string in php. I know if youre trying to select a value from a column you need to enclose it in double quotes if youre doing it directly in sql. in this case the value in question is being passed as a variable from a previous page. i have the current line for selection in my page, but i dont think it is correct. any help? $login = mysql_query(SELECT * FROM Users WHERE Username =. $User_Name) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: SELECT giving too much :)
SELECT distinct(students.fname), courses.title AS course FROM students, courses, course_enrolments, groups, locations, staff WHERE (students.studentID=course_enrolments.studentID AND course_enrolments.courseID=courses.courseID) AND (students.groupID=groups.groupID) AND (staff.staffID=courses.staffID) ORDER BY fname ASC should do iy Steve Beau Lebens [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... Hi guys, I have a set-up to query a database by allowing the user to build a query from selecting fields, restrictions etc etc. It produces the following; SELECT students.fname, courses.title AS course FROM students, courses, course_enrolments, groups, locations, staff WHERE (students.studentID=course_enrolments.studentID AND course_enrolments.courseID=courses.courseID) AND (students.groupID=groups.groupID) AND (staff.staffID=courses.staffID) ORDER BY fname ASC which gets the right results, but it returns something like this; +---+--+ | fname | course | +---+--+ | Beau | Graduate Certificate in Learning Technologies (K-12) | | Beau | Graduate Certificate in Learning Technologies (K-12) | | Grant | Short Course in Learning Technologies (K-12) | | Grant | Short Course in Learning Technologies (K-12) | +---+--+ and it should only be returning one of each of those records any suggestions? thanks Beau -- Beau F Lebens, Technical Officer National Key Centre for School Science and Mathematics Science and Mathematics Education Centre Curtin University of Technology, GPO Box U1987 Perth, Western Australia 6845 t: +61 8 9266 7297 (has voice-mail) f: +61 8 9266-2503 (ATT: Beau Lebens) e: [EMAIL PROTECTED] w: http://learnt.smec.curtin.edu.au/ -- PHP Database 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-DB] Re: Select Inner Join Question
Steve Fitzgerald [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... I'm trying to display a summary list of calls with one attribute being CallType. I have CallType and CallTypeID defined in a table name calltypes. The problem I am having is taking the output of CallID defined in calls and having the script match the CallTypeID to the CallType. $display_calls_sql = SELECT CallID,CallDateTime,CallSubject,CallStatus FROM calls WHERE ContactID = $ContactID; $display_calltype_sql = SELECT CallType FROM calltypes INNER JOIN calls ON calltypes.CallTypeID = calls.CallTypeID WHERE CallID = '$CallID' ; $display_calls_sql = SELECT .CallID, CallDateTime, CallSubject, CallStatus, CallType .FROM .calls INNER JOIN calltypes ON call.CallTypeID = calltypes.CallTypeID .WHERE .ContactID = $ContactID; -- PHP Database 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-DB] Re: Select just the first two letters in a column (MySQL)
On Thu, 8 Mar 2001 08:47:41 -0600, John Guynn wrote: Is there a way to select just the first two letters in a column? I have a column that contains values like TT1, TT2, TT3, TT4, PR1, PR2, RD1, RD2, RD3, and I'd like to SELECT DISTINCT the first two letters. In otherwords have the select statement return TT, PR, RD. -- Rod Buchanan Programmer/Analyst/List Manager/JOATMON KDS Internet Services http://www.kdsi.net -- PHP Database 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]
