RE: SQL Syntax Question
Thank you for trying to help me.
The output is wrong
I get either
Event 1
Event 2
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Or
Event 1
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Event 2
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
But not what I need
Event 1
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Event 2
Details 1 for event 2
Details 2 for event 2
Details 3 for event 2
-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 04, 2004 12:08 AM
To: Karl-Heinz Schulz; [EMAIL PROTECTED]
Subject: Re: SQL Syntax Question
- Original Message -
From: Karl-Heinz Schulz [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, August 03, 2004 9:18 PM
Subject: SQL Syntax Question
I tried to get an answer on the PHP mailing list and I was told that this
list would be quicker to get me a solution.
I have two tables Event and Eventdetails (structures dump can be found
at the end of the message).
I want to display all events and the related information from the
eventdetails table like
Event 1
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Event 2
Details 1 for event 2
Details 2 for event 2
Details 3 for event 2
Etc.
I cannot figure it out.
Here is my PHP code.
--
--
?php
require(../admin/functions.php);
include(../admin/header.inc.php);
?
?
$event_query = mysql_query(select id, inserted, information, eventname,
date, title from event order by inserted desc LIMIT 0 , 30);
while($event = mysql_fetch_row($event_query)){
print(bspan style=\font-family: Arial, Helvetica,
sans-serif;color:#003300;font-size:14px;\.html_decode($event[5])./span
/bbr);
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($event[4])./spanbr);
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($event[2])./spanp);
$eventdetail_query = mysql_query(select informations, titles, file_name
from eventdetail, event where eventdetail.event =.$event[0]);
//$eventdetail_query = mysql_query(select titles, informations, file_name
from eventdetail, event where eventdetail.event = event.id);
while($eventdetail = mysql_fetch_row($eventdetail_query)){
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($eventdetail[0])./span);
print(nbspspan style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($eventdetail[1])./span);
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($eventdetail[2])./spanp);
}
}
?
--
--
What am I missing?
TIA
Karl-Heinz
#
# Table structure for table `event`
#
CREATE TABLE event (
id smallint(2) unsigned NOT NULL auto_increment,
veranstaltung smallint(2) unsigned NOT NULL default '0',
inserted date NOT NULL default '-00-00',
information text NOT NULL,
eventname text NOT NULL,
date varchar(30) NOT NULL default '',
title varchar(100) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
#
# Table structure for table `eventdetail`
#
CREATE TABLE eventdetail (
id smallint(2) unsigned NOT NULL auto_increment,
event smallint(2) NOT NULL default '0',
informations text NOT NULL,
titles varchar(100) NOT NULL default '',
file_name varchar(100) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
Tracking #: 5CF2A36BDC27D14BA1C3A19CBAC7214ED510CB7E
What you've already given us is great but it would really help if you
described the problem you are encountering. It's not clear whether you are
getting error messages from MySQL or your result sets simply don't match
your expectations or if you are getting compile errors from php.
If you could state just what the problem is, and ideally show the result you
are getting (if any) versus the result you expected, it would be easier to
help you.
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax Question
Karl-Heinz Schulz wrote: Thank you for trying to help me. The output is wrong I get either Event 1 Event 2 Details 1 for event 1 Details 2 for event 1 Details 3 for event 1 that query is wrong : $eventdetail_query = mysql_query(select informations, titles, file_name from eventdetail, event where eventdetail.event =.$event[0]); try : select informations, titles, file_name from eventdetail, event where event.id=.$event[0] AND event.id=eventdetails.event -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Syntax Question
Philippe,
I changed my to the following but the result is now (I deleted the print
stuff for better reading)
?
$event_query = mysql_query(select id, inserted, information, eventname,
date, title from event order by inserted desc LIMIT 0 , 30);
while($event = mysql_fetch_row($event_query)){
$eventdetail_query = mysql_query(select titles, informations, file_name
from eventdetail, event where event.id=eventdetail.event AND
event.id=.$event[0]);
while($eventdetail = mysql_fetch_row($eventdetail_query)){
}
}
?
Event 1
Event 2
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
But I would need
Event 1
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Event 2
Details 1 for event 2
Details 2 for event 2
Details 3 for event 2
Is this even possible?
TIA
-Original Message-
From: Philippe Poelvoorde [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 04, 2004 5:52 AM
To: Karl-Heinz Schulz
Cc: [EMAIL PROTECTED]
Subject: Re: SQL Syntax Question
Karl-Heinz Schulz wrote:
Thank you for trying to help me.
The output is wrong
I get either
Event 1
Event 2
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
that query is wrong :
$eventdetail_query = mysql_query(select informations, titles, file_name
from eventdetail, event where eventdetail.event =.$event[0]);
try :
select informations, titles, file_name
from eventdetail, event where event.id=.$event[0] AND
event.id=eventdetails.event
Tracking #: 3842A5D2EB81014B918FDB71F1DE0830A35E8D56
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax Question
- Original Message -
From: Karl-Heinz Schulz [EMAIL PROTECTED]
To: 'Philippe Poelvoorde' [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, August 04, 2004 6:41 AM
Subject: RE: SQL Syntax Question
Philippe,
I changed my to the following but the result is now (I deleted the print
stuff for better reading)
?
$event_query = mysql_query(select id, inserted, information, eventname,
date, title from event order by inserted desc LIMIT 0 , 30);
while($event = mysql_fetch_row($event_query)){
$eventdetail_query = mysql_query(select titles, informations, file_name
from eventdetail, event where event.id=eventdetail.event AND
event.id=.$event[0]);
while($eventdetail = mysql_fetch_row($eventdetail_query)){
}
}
?
Karl-Heinz,
I used the following SQL in a script and got the answer that I think you
want:
select informations, titles, file_name
from eventdetail d inner join event e on e.veranastaltung = d.event
where d.event = 1
This gave me just the eventdetails for event 1.
This is not in php format of course. I don't know php but it looks similar
to other languages I know so I'm guessing that you would write it as follows
in php:
$eventdetail_query = mysql_query(select titles, informations, file_name
from eventdetail d inner join event e on e.veranstaltung = d.event
where event.id=.$event[0]);
Explanation:
Since you named two tables in the 'from' clause of the eventdetail query,
you are clearly attempting to join the tables. I'm assuming you want an
inner join. In other words, you only want to show details if there is a
corresponding event row that matches your detail row. To get a proper join,
you need to identify what the two tables have in common. If I understand
your data correctly, the veranstaltung column in the Event table is going to
have the same value as the event column in the Eventdetail table when the
rows are describing the same event. Therefore, that is what I put in the
'on' clause of the query. The 'where' clause is the one I'm least sure how
to write in php but, based on what you had in your queries, I assume that
this is the way to tell the query to return only rows where the event column
in the join result has the same value as the event value in the event row
currently being processed in the outer loop.
In short, you were doing a join implicitly but hadn't properly specified the
joining condition so you weren't getting the rows you really wanted.
By the way, I really wasn't completely clear on the meaning of the data in
the tables so I made some guesses about the contents of each column. This is
the script I wrote to create and populate the tables. Your original event
query, which is unchanged, appears after that and my best guess for the
eventdetail query is at the end.
-
use tmp;
#Event table contains one row for each event.
select 'Drop/create Event table';
drop table if exists event;
create table if not exists event
(id smallint(2) unsigned not null auto_increment,
veranstaltung smallint(2) not null default '0',
inserted date not null default '-00-00',
information text not null,
eventname text not null,
date varchar(30) not null default '',
title varchar(100) not null default '',
primary key(id)
) TYPE=MyISAM;
select 'Populate Event table';
insert into event (veranstaltung, inserted, information, eventname, date,
title) values
(1, '2004-04-20', 'information-01', 'Canada Day', '2004-07-01', 'title-01'),
(2, '2004-05-03', 'information-02', 'Labour Day', '2004-09-04', 'title-02'),
(3, '2004-08-15', 'information-03', 'Christmas Day', '2004-12-25',
'title-03');
select 'Display Event table';
select * from event;
#Event_Detail table contains one row for each aspect of an event.
select 'Drop/create Eventdetail table';
drop table if exists eventdetail;
create table if not exists eventdetail
(id smallint(2) unsigned not null auto_increment,
event smallint(2) not null default '0',
informations text not null,
titles varchar(100) not null default '',
file_name varchar(100) not null default '',
primary key(id)
) TYPE=MyISAM;
select 'Populate Eventdetail table';
insert into eventdetail (event, informations, titles, file_name) values
(1, 'information-01a', 'title-01a', 'file-01a'),
(1, 'information-01b', 'title-01b', 'file-01b'),
(1, 'information-01c', 'title-01c', 'file-01c'),
(2, 'information-02a', 'title-02a', 'file-02a'),
(2, 'information-02b', 'title-02b', 'file-02b'),
(2, 'information-02c', 'title-02c', 'file-02c'),
(3, 'information-03a', 'title-03a', 'file-03a'),
(3, 'information-03b', 'title-03b', 'file-03b'),
(3, 'information-03c', 'title-03c', 'file-03c');
select 'Display Eventdetail table';
select * from eventdetail;
select 'Event query';
select id, inserted, information, eventname, date, title
from event
order by inserted desc limit 0, 30;
select 'Eventdetail query';
select informations, titles, file_name
Re: SQL Syntax Question
- Original Message -
From: Karl-Heinz Schulz [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, August 03, 2004 9:18 PM
Subject: SQL Syntax Question
I tried to get an answer on the PHP mailing list and I was told that this
list would be quicker to get me a solution.
I have two tables Event and Eventdetails (structures dump can be found
at the end of the message).
I want to display all events and the related information from the
eventdetails table like
Event 1
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Event 2
Details 1 for event 2
Details 2 for event 2
Details 3 for event 2
Etc.
I cannot figure it out.
Here is my PHP code.
--
--
?php
require(../admin/functions.php);
include(../admin/header.inc.php);
?
?
$event_query = mysql_query(select id, inserted, information, eventname,
date, title from event order by inserted desc LIMIT 0 , 30);
while($event = mysql_fetch_row($event_query)){
print(bspan style=\font-family: Arial, Helvetica,
sans-serif;color:#003300;font-size:14px;\.html_decode($event[5])./span
/bbr);
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($event[4])./spanbr);
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($event[2])./spanp);
$eventdetail_query = mysql_query(select informations, titles, file_name
from eventdetail, event where eventdetail.event =.$event[0]);
//$eventdetail_query = mysql_query(select titles, informations, file_name
from eventdetail, event where eventdetail.event = event.id);
while($eventdetail = mysql_fetch_row($eventdetail_query)){
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($eventdetail[0])./span);
print(nbspspan style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($eventdetail[1])./span);
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($eventdetail[2])./spanp);
}
}
?
--
--
What am I missing?
TIA
Karl-Heinz
#
# Table structure for table `event`
#
CREATE TABLE event (
id smallint(2) unsigned NOT NULL auto_increment,
veranstaltung smallint(2) unsigned NOT NULL default '0',
inserted date NOT NULL default '-00-00',
information text NOT NULL,
eventname text NOT NULL,
date varchar(30) NOT NULL default '',
title varchar(100) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
#
# Table structure for table `eventdetail`
#
CREATE TABLE eventdetail (
id smallint(2) unsigned NOT NULL auto_increment,
event smallint(2) NOT NULL default '0',
informations text NOT NULL,
titles varchar(100) NOT NULL default '',
file_name varchar(100) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
Tracking #: 5CF2A36BDC27D14BA1C3A19CBAC7214ED510CB7E
What you've already given us is great but it would really help if you
described the problem you are encountering. It's not clear whether you are
getting error messages from MySQL or your result sets simply don't match
your expectations or if you are getting compile errors from php.
If you could state just what the problem is, and ideally show the result you
are getting (if any) versus the result you expected, it would be easier to
help you.
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax question
Graham, Monday, May 13, 2002, 8:45:09 PM, you wrote: GN I have a directory of professional magicians, consisting of a MySQL table GN like GN this: GN +-++-+ GN | artist | area| magic | GN +-++-+ GN | Joe Bloggs | AZ*IN*TX | childrens | GN +-++-+ GN | Fred Smith | All | close-up | GN +-++-+ GN A surfer will select an area and then the type of magic they require via a GN php/HTML form. So to locate a performer who does magic for children in Texas GN I use GN SELECT * FROM artist WHERE (area LIKE '%$area%' OR area = 'All') AND magic GN LIKE '%$magic%' GN (where $area and $magic are variables passed from PHP). GN However, this does not give the desired result, it just returns any perfomer GN who does magic for children. What am I doing wrong please? I've tried GN several other syntax combinations without success. What are the values of your php variables? Are you sure that they are correct? GN kind regards, GN Graham Nichols. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL syntax question
At 10:02 AM -0800 12/18/01, Steve Osborne wrote:
I would like to use an input form to add users to my database, however, if
the name is already in use, I do not want to add a duplicate record. I also
need this to be case insensitive (ie Santa Claus = santa Claus).
Make the (LastName, FirstName) a primary key and use INSERT IGNORE. Then test
mysql_affected_rows() to see whether it's 1 or 0. If it's 1,
the record was inserted. If it's 0, you tried to insert a dup.
I've tried the following code, but it doesn't seem to be working
$chknamerow = mysql_fetch_array(runsql(SELECT FirstName,LastName FROM Names
WHERE FirstName LIKE '$addfirstname' AND LastName LIKE '$addlastname' ));
$chkname = $chknamerow[FirstName] . $chknamerow[LastName];
if( ($chknamerow[FirstName]) AND ($chknamerow[LastName]) )
{
$Evalname = $addfirstname2 . $addlastname2;
$evalchange = is ALREADY entered as ;
$NewName = $chkname;
printf(p class=\subtitle\The name %s was not added to the
database./p\n, $Evalname);
}
The function runsql() is as follows:
function runsql($query)
{
global $debugit;
global $dbname;
global $mysql_link;
$runresult = mysql_db_query($dbname, $query, $mysql_link);
if (($debugit ) AND ($runresult == ))
{
mysql_error($mysql_link);
echo mysql_errno().: .mysql_error($mysql_link). on database
$dbnameBR;
echo While running SQL: $queryBR;
}
return ($runresult);
}
Any advice?
Steve Osborne
[EMAIL PROTECTED]
?php
/* Happy Holidays */
mysql_select_db('North_Pole');
mysql_query('SELECT reindeer FROM stable WHERE nose_color=red');
?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SQL Syntax question
On 26-Apr-01 [EMAIL PROTECTED] wrote: I have two table I need to join in a query. The second table needs to be join twice (I think) to the first. Details as follows (tables pared down)... Table games gameid hometeamid guestteamid Table team teamid sponsor I want a query to return game.gamid, team.sponsor (hometeam), team.sponsor (guestteam). can someone point me in the right direction for this please? select gameid,home.sponsor,guest.sponsor from games,team as home,team as guest where hometeamid=home.teamid and guestteamid=guest.teamid; Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL Syntax question
Hi there, I'm using mysql 3.22.27 and get error when trying to run this select statement: SELECT custmls.mlsnumber,custmls.streetnumber,custmls.streetdirect, FORMAT(custmls.currentprice,0),custmls.streetnam,custmls.streetaddtl, custmls.municname,custmls.state,custmls.zipcd,custmls.salesassoc, ipix.url,custmls.listagentname,FORMAT(custmls.numrooms,0), FORMAT(custmls.numbedrooms,0),custmls.fullbaths FROM custmls,ipix where custmls.mlsnumber = ipix.mlsnumber AND custmls.listingoffice = 0251 AND (custmls.listingstatus = 'ACT' or custmls.listingstatus = 'A*') AND custmls.scategory = 1 ORDER BY custmls.currentprice The error is: "1064 You have an error in your SQL syntax near 'ON ipix custmls.mlsnumber = ipix.mlsnumber where custmls.listingoffice = 0251 AN' at line 1 " Any ideas? Thanks Pat Sir, the error message was obviously from an SQL statement other than the one you quote above. Since I don't know your table structure, I can't be sure what the problem is, but it looks like you are writing columns and tables as table_name.column_name. This is backwards. It should be column_name.table_name. The alternative is that you are selecting from a boat load of tables that aren't mentioned in the FROM clause. I see two problems with the snippet of SQL quoted in the error message. First of all, the word 'ipix' after ON isn't serving any function, other than to confuse MySQL. Secondly, you are trying to join two tables, but your ON clause joins a column from mlsnumber to another column from mlsnumber. If this is not the error mentioned above, then you need to join a column in mlsnumber to a column in the second table. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
