Query Error

[Wm]

I have a query that I expect to return 3 or 4 entries -- but I seem to be
getting only the most recent entry, repeated 4 times. What am I doing wrong
here?

$query="SELECT artistID,email,city,state,country from artists WHERE
email='$email'";
$result=mysql_query($query) or die(mysql_error("Could not execute
query."));
if (mysql_num_rows($result) > 0){
$alreadylisted = "1";
echo "<CENTER>$email is already in our database. Are you listed
below?</CENTER><BR>";
while($row = mysql_fetch_array($result)) {
$artistID = $row['artistID'];
$email = $row['email'];
$city = $row['city'];
$state = $row['state'];
$country = $row['country'];
echo "<CENTER><HR><FONT size=\"2\" face=\"Arial, Helvetica,
sans-serif\">
<A HREF=\"javascript:;\"
onClick=\"openProfile('artist.php?artistID=".$arti stID.

"','Artist','resizable=yes,width=600,height=300')\ ">".$firstname."
".$lastname."</A><BR>"
.$city.", ".$state." ".$country."</FONT><BR></CENTER>";
}
echo "<HR><CENTER>If you are already listed above,
congratulations!<BR>
If needed, you may edit your listing using the link at
left.</CENTER>";
mysql_free_result($result);
}


Thanx,
Wm

[DjDrakk]

try echoing your query to screen so you can read exactly what is being sent
to the database beforehand.

--

Warren Butt
-- Custom web design, cheap like cheese


"Wm" <LAshooter@hotmail.com> wrote in message
news:1haYa.1802083$ZC.268155@news.easynews.com...

> I have a query that I expect to return 3 or 4 entries -- but I seem to be
> getting only the most recent entry, repeated 4 times. What am I doing

wrong

> here?
>
> $query="SELECT artistID,email,city,state,country from artists WHERE
> email='$email'";
> $result=mysql_query($query) or die(mysql_error("Could not execute
> query."));
> if (mysql_num_rows($result) > 0){
> $alreadylisted = "1";
> echo "<CENTER>$email is already in our database. Are you listed
> below?</CENTER><BR>";
> while($row = mysql_fetch_array($result)) {
> $artistID = $row['artistID'];
> $email = $row['email'];
> $city = $row['city'];
> $state = $row['state'];
> $country = $row['country'];
> echo "<CENTER><HR><FONT size=\"2\" face=\"Arial, Helvetica,
> sans-serif\">
> <A HREF=\"javascript:;\"
> onClick=\"openProfile('artist.php?artistID=".$arti stID.
>
> "','Artist','resizable=yes,width=600,height=300')\ ">".$firstname."
> ".$lastname."</A><BR>"
> .$city.", ".$state." ".$country."</FONT><BR></CENTER>";
> }
> echo "<HR><CENTER>If you are already listed above,
> congratulations!<BR>
> If needed, you may edit your listing using the link at
> left.</CENTER>";
> mysql_free_result($result);
> }
>
>
> Thanx,
> Wm
>
>
>

[Wm]

"DjDrakk" <DjDrakk@drakkradio.servemp3.com> wrote in message
news:vj2dmnopl9h08d@corp.supernews.com...

> try echoing your query to screen so you can read exactly what is being

sent

> to the database beforehand.
>
> Warren Butt
> -- Custom web design, cheap like cheese
>

echo "Query result = ".$result;

yields this output:

ArrayQuery result = Resource id #21

I'm not sure where the word "Array" is coming from, nor what the "Resource
id #21" is.....????

Wm

>
> "Wm" <LAshooter@hotmail.com> wrote in message
> news:1haYa.1802083$ZC.268155@news.easynews.com...

> > I have a query that I expect to return 3 or 4 entries -- but I seem to

be

> > getting only the most recent entry, repeated 4 times. What am I doing

> wrong

> > here?
> >
> > $query="SELECT artistID,email,city,state,country from artists WHERE
> > email='$email'";
> > $result=mysql_query($query) or die(mysql_error("Could not execute
> > query."));
> > if (mysql_num_rows($result) > 0){
> > $alreadylisted = "1";
> > echo "<CENTER>$email is already in our database. Are you listed
> > below?</CENTER><BR>";
> > while($row = mysql_fetch_array($result)) {
> > $artistID = $row['artistID'];
> > $email = $row['email'];
> > $city = $row['city'];
> > $state = $row['state'];
> > $country = $row['country'];
> > echo "<CENTER><HR><FONT size=\"2\" face=\"Arial, Helvetica,
> > sans-serif\">
> > <A HREF=\"javascript:;\"
> > onClick=\"openProfile('artist.php?artistID=".$arti stID.
> >
> > "','Artist','resizable=yes,width=600,height=300')\ ">".$firstname."
> > ".$lastname."</A><BR>"
> > .$city.", ".$state." ".$country."</FONT><BR></CENTER>";
> > }
> > echo "<HR><CENTER>If you are already listed above,
> > congratulations!<BR>
> > If needed, you may edit your listing using the link at
> > left.</CENTER>";
> > mysql_free_result($result);
> > }
> >
> >
> > Thanx,
> > Wm
> >
> >
> >

>
>

[Wm]

"Trent Stith" <trents@airmail.net> wrote in message
news:bgribl$d9c@library2.airnews.net...

> You need to echo the query itself, not the result of the query.
>
> echo "<pre>";
> echo $query;
>
> that will return what your query is actually doing it may shed some light.
>

I pasted the lines above in after the query and got this result output to
the browser:

"Array
[email]address@hotmail.com[/email] is already in our database. Are you listed below?"

I'm still not sure why I'm getting the word "Array" output, nor why I don't
see the query that I'm trying to verify. And I'm still getting the same info
output 4 times rather than 4 separate listings, but I'm sure that's a
different problem... <groan>

Thanx,
Wm


************************************************** ********
ORIGINAL MESSAGE(S):
************************************************** ********

> "Wm" <LAshooter@hotmail.com> wrote in message
> news:oLaYa.1378547$Ho4.9675373@news.easynews.com.. .

> > "DjDrakk" <DjDrakk@drakkradio.servemp3.com> wrote in message
> > news:vj2dmnopl9h08d@corp.supernews.com...

> > > try echoing your query to screen so you can read exactly what is being

> > sent

> > > to the database beforehand.
> > >
> > > Warren Butt
> > > -- Custom web design, cheap like cheese
> > >

> >
> > echo "Query result = ".$result;
> >
> > yields this output:
> >
> > ArrayQuery result = Resource id #21
> >
> > I'm not sure where the word "Array" is coming from, nor what the

"Resource

> > id #21" is.....????
> >
> > Wm
> >
> >
> >

> > >
> > > "Wm" <LAshooter@hotmail.com> wrote in message
> > > news:1haYa.1802083$ZC.268155@news.easynews.com...
> > > > I have a query that I expect to return 3 or 4 entries -- but I seem

to

> > be

> > > > getting only the most recent entry, repeated 4 times. What am I

doing

> > > wrong
> > > > here?
> > > >
> > > > $query="SELECT artistID,email,city,state,country from artists

WHERE

> > > > email='$email'";
> > > > $result=mysql_query($query) or die(mysql_error("Could not execute
> > > > query."));
> > > > if (mysql_num_rows($result) > 0){
> > > > $alreadylisted = "1";
> > > > echo "<CENTER>$email is already in our database. Are you

listed

> > > > below?</CENTER><BR>";
> > > > while($row = mysql_fetch_array($result)) {
> > > > $artistID = $row['artistID'];
> > > > $email = $row['email'];
> > > > $city = $row['city'];
> > > > $state = $row['state'];
> > > > $country = $row['country'];
> > > > echo "<CENTER><HR><FONT size=\"2\" face=\"Arial,

Helvetica,

> > > > sans-serif\">
> > > > <A HREF=\"javascript:;\"
> > > > onClick=\"openProfile('artist.php?artistID=".$arti stID.
> > > >
> > > > "','Artist','resizable=yes,width=600,height=300')\ ">".$firstname."
> > > > ".$lastname."</A><BR>"
> > > > .$city.", ".$state." ".$country."</FONT><BR></CENTER>";
> > > > }
> > > > echo "<HR><CENTER>If you are already listed above,
> > > > congratulations!<BR>
> > > > If needed, you may edit your listing using the link at
> > > > left.</CENTER>";
> > > > mysql_free_result($result);
> > > > }
> > > >
> > > >
> > > > Thanx,
> > > > Wm
> > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[James Jiao]

Ok insert the echo "$query" line in the following places where indicated
with 'echo', that's how you should isolate the problem to one line of code:


"Wm" <LAshooter@hotmail.com> wrote in message
news:1haYa.1802083$ZC.268155@news.easynews.com...

> I have a query that I expect to return 3 or 4 entries -- but I seem to be
> getting only the most recent entry, repeated 4 times. What am I doing

wrong

> here?
>

echo

> $query="SELECT artistID,email,city,state,country from artists WHERE
> email='$email'";

echo

> $result=mysql_query($query) or die(mysql_error("Could not execute
> query."));

echo

> if (mysql_num_rows($result) > 0){
> $alreadylisted = "1";
> echo "<CENTER>$email is already in our database. Are you listed
> below?</CENTER><BR>";
> while($row = mysql_fetch_array($result)) {
> $artistID = $row['artistID'];
> $email = $row['email'];
> $city = $row['city'];
> $state = $row['state'];
> $country = $row['country'];
> echo "<CENTER><HR><FONT size=\"2\" face=\"Arial, Helvetica,
> sans-serif\">
> <A HREF=\"javascript:;\"
> onClick=\"openProfile('artist.php?artistID=".$arti stID.
>
> "','Artist','resizable=yes,width=600,height=300')\ ">".$firstname."
> ".$lastname."</A><BR>"
> .$city.", ".$state." ".$country."</FONT><BR></CENTER>";
> }
> echo "<HR><CENTER>If you are already listed above,
> congratulations!<BR>
> If needed, you may edit your listing using the link at
> left.</CENTER>";
> mysql_free_result($result);
> }
>
>
> Thanx,
> Wm
>
>
>

[Stijn Goris]

I get an error when trying to insert a new row:
query error- 5 - Duplicate entry '80.200.213.102 Mozilla/4.0 (compatible;
MSIE 6.0; Windows NT 5.1; Avant Browser [avantbrowser.com]; MyIE2; .NET CLR
1.0.' for key 1

The table consist of timestamp, file, level, ip, naam , ID


Where ID is an autoincrement, unique key.
Someone has an idea what going wrong?

kind regards
Stijn

[Erwin Moller]

Stijn Goris wrote:

> I get an error when trying to insert a new row:
> query error- 5 - Duplicate entry '80.200.213.102 Mozilla/4.0 (compatible;
> MSIE 6.0; Windows NT 5.1; Avant Browser [avantbrowser.com]; MyIE2; .NET
> CLR 1.0.' for key 1
>
> The table consist of timestamp, file, level, ip, naam , ID
>
>
> Where ID is an autoincrement, unique key.
> Someone has an idea what going wrong?

Yes, you made the column where this string is inserted in UNIQUE too, and it
already contained that value.
Check your tabledefinition, constraints and such.

>
> kind regards
> Stijn

Regards,
Erwin

[Jahcoldf]

I am having a problem with updating my database. I cant narrow down the
problem. I'll take any advice thanks.

I am sorting the data then updating it with the new order made by the sorting.

Code:
<cfquery name="get_menu" datasource="#db#">
SELECT *
FROM menu
ORDER BY LVL1, LVL;
</cfquery>


<cfoutput query="get_menu" maxrows="1">
<cfquery name="updt_menu1" datasource="#db#">
UPDATE Menu
SET Order = '#recordcount#'
WHERE mid = #mid#;
</cfquery>
</cfoutput>

Error:
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access
Driver] Syntax error in WHERE clause.

The Error Occurred in D:\CFusionMX\wwwroot\Hambleton\menu.cfm: line 13
Called from D:\CFusionMX\wwwroot\Hambleton\index.cfm: line 9
Called from D:\CFusionMX\wwwroot\Hambleton\menu.cfm: line 13
Called from D:\CFusionMX\wwwroot\Hambleton\index.cfm: line 9

11 : SET LVL = '#recordcount#'
12 : WHERE order = 3;
13 : </cfquery>
14 : #LVL# #mid# #value# #LVL1# #LVL2#<br>
15 : </cfoutput>

[The ScareCrow]

Because you have maxrows in the cfoutput query, it will only ever do the one
record.

I am also assuming that the "Order" column is numeric and thus does not have
single quotes

<cfoutput query="get_menu">
<cfquery name="updt_menu1" datasource="#db#">
UPDATE Menu
SET Order = #recordcount#
WHERE mid = #mid#;
</cfquery>
</cfoutput>

Ken

[Jahcoldf]

I fixed the where clause and i am still getting some trouble.

I changed the code to this:
<cfquery name="get_menu" datasource="#db#">
SELECT *
FROM menu
ORDER BY LVL1, LVL;
</cfquery>
<cfinclude template="menu-order.cfm">

<cfoutput query="get_menu" maxrows="1">
<cfquery name="updt_menu1" datasource="#db#">
UPDATE Menu
SET Order = #recordcount#
WHERE mid = #mid#
</cfquery>
#LVL# #mid# #value# #LVL1# #LVL2#<br>
</cfoutput>

I get this error:
Error Executing Database Query.
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access
Driver] Syntax error in UPDATE statement.

The Error Occurred in D:\CFusionMX\wwwroot\Hambleton\menu.cfm: line 13

11 : SET Order = #recordcount#
12 : WHERE mid = #mid#
13 : </cfquery>
14 : #LVL# #mid# #value# #LVL1# #LVL2#<br>
15 : </cfoutput>




--------------------------------------------------------------------------------

SQL UPDATE Menu SET Order = 7 WHERE mid = 1001
DATASOURCE hambleton
VENDORERRORCODE -3503
SQLSTATE 42000

[The ScareCrow]

Your query now appears to be correct. I would assume that you may have a
problem with your table and column names, they may be reserved words, so

<cfoutput query="get_menu" maxrows="1">
<cfquery name="updt_menu1" datasource="#db#">
UPDATE [Menu]
SET [Order] = #recordcount#
WHERE [mid] = #mid#
</cfquery>
#LVL# #mid# #value# #LVL1# #LVL2#<br>
</cfoutput>

The above should resolve this.

Ken

[kyle969]

Both mid and order are reserved words in SQL. the problem may be there.

[maux]

hi how can i fix this?

Errore

query SQL :

CREATE TABLE `tags` (
`tag_link_id` int(11) NOT NULL default '0',
`tag_lang` varchar(4) NOT NULL default 'en',
`tag_date` timestamp NOT NULL,
`tag_words` varchar(64) NOT NULL default '',
UNIQUE KEY `tag_link_id` (`tag_link_id`,`tag_lang`,`tag_words`),
KEY `tag_lang` (`tag_lang`,`tag_date`)
) ENGINE=MyISAM

Messaggio di MySQL:


You have an error in your SQL syntax near 'ENGINE=MyISAM' at line 8

[Brian Wakem]

maux wrote:

> hi how can i fix this?
>
> Errore
>
> query SQL :
>
> CREATE TABLE `tags` (
> `tag_link_id` int(11) NOT NULL default '0',
> `tag_lang` varchar(4) NOT NULL default 'en',
> `tag_date` timestamp NOT NULL,
> `tag_words` varchar(64) NOT NULL default '',
> UNIQUE KEY `tag_link_id` (`tag_link_id`,`tag_lang`,`tag_words`),
> KEY `tag_lang` (`tag_lang`,`tag_date`)
> ) ENGINE=MyISAM
>
> Messaggio di MySQL:
>
>
> You have an error in your SQL syntax near 'ENGINE=MyISAM' at line 8

Works for me.


mysql> CREATE TABLE `tags` ( `tag_link_id` int(11) NOT NULL default '0',
`tag_lang` varchar(4) NOT NULL default 'en', `tag_date` timestamp NOT
NULL, `tag_words` varchar(64) NOT NULL default '', UNIQUE KEY
`tag_link_id` (`tag_link_id`,`tag_lang`,`tag_words`), KEY `tag_lang`
(`tag_lang`,`tag_date`) ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql>


Perhaps you are using a pre-myisam version (<= 3.22 I think)?


--
Brian Wakem
Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]

[Kai Ruhnau]

maux wrote:

> hi how can i fix this?
>
> Errore
>
> query SQL :
>
> CREATE TABLE `tags` (
> `tag_link_id` int(11) NOT NULL default '0',
> `tag_lang` varchar(4) NOT NULL default 'en',
> `tag_date` timestamp NOT NULL,
> `tag_words` varchar(64) NOT NULL default '',
> UNIQUE KEY `tag_link_id` (`tag_link_id`,`tag_lang`,`tag_words`),
> KEY `tag_lang` (`tag_lang`,`tag_date`)
> ) ENGINE=MyISAM
>
> Messaggio di MySQL:
>
>
> You have an error in your SQL syntax near 'ENGINE=MyISAM' at line 8

Use a MySQL version that supports ENGINE.
All versions before 4.1 don't support ENGINE.

Greetins
Kai

[maux]

Brian Wakem scriveva il 21/02/2006 :

> maux wrote:
>

>> hi how can i fix this?
>>
>> Errore
>>
>> query SQL :
>>
>> CREATE TABLE `tags` (
>> `tag_link_id` int(11) NOT NULL default '0',
>> `tag_lang` varchar(4) NOT NULL default 'en',
>> `tag_date` timestamp NOT NULL,
>> `tag_words` varchar(64) NOT NULL default '',
>> UNIQUE KEY `tag_link_id` (`tag_link_id`,`tag_lang`,`tag_words`),
>> KEY `tag_lang` (`tag_lang`,`tag_date`)
>> ) ENGINE=MyISAM
>>
>> Messaggio di MySQL:
>>
>>
>> You have an error in your SQL syntax near 'ENGINE=MyISAM' at line 8

>
>
> Works for me.
>
>
> mysql> CREATE TABLE `tags` ( `tag_link_id` int(11) NOT NULL default '0',
> `tag_lang` varchar(4) NOT NULL default 'en', `tag_date` timestamp NOT
> NULL, `tag_words` varchar(64) NOT NULL default '', UNIQUE KEY
> `tag_link_id` (`tag_link_id`,`tag_lang`,`tag_words`), KEY `tag_lang`
> (`tag_lang`,`tag_date`) ) ENGINE=MyISAM;
> Query OK, 0 rows affected (0.01 sec)
>
> mysql>
>
>
> Perhaps you are using a pre-myisam version (<= 3.22 I think)?

yes u have right! thanks

[maux]

Il 21/02/2006, Kai Ruhnau ha detto :

> maux wrote:

>> hi how can i fix this?
>>
>> Errore
>>
>> query SQL :
>>
>> CREATE TABLE `tags` (
>> `tag_link_id` int(11) NOT NULL default '0',
>> `tag_lang` varchar(4) NOT NULL default 'en',
>> `tag_date` timestamp NOT NULL,
>> `tag_words` varchar(64) NOT NULL default '',
>> UNIQUE KEY `tag_link_id` (`tag_link_id`,`tag_lang`,`tag_words`),
>> KEY `tag_lang` (`tag_lang`,`tag_date`)
>> ) ENGINE=MyISAM
>>
>> Messaggio di MySQL:
>>
>>
>> You have an error in your SQL syntax near 'ENGINE=MyISAM' at line 8

>
> Use a MySQL version that supports ENGINE.
> All versions before 4.1 don't support ENGINE.
>
> Greetins
> Kai

ok thanks!