Re: [PHP-DB] MySQL / PHP Database Help
Hello,
something that you can help is the follow
try var_dump($newquery);
then you see the complete query and run it in phpmyadmin.
Jeroen
- Original Message -
From: Jonathan Underfoot [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, February 08, 2002 2:43 PM
Subject: [PHP-DB] MySQL / PHP Database Help
I'm trying to write a script that checks for multiple entries on a table in
a database. So far I've been plagued by MySQL errors. I'm fairly confident
my scripting is ok (Then again, I've been wrong before.) I think moreover
there might be some difficulty with my theory. Then again, I could try and
do this with MySQL join statements. Any feedback would be appreciated.
$sqlquery = mysql_query(SELECT * FROM local_shows);
while ($row = mysql_fetch_array($sqlquery)) {
$newquery = mysql_query(SELECT * FROM local_shows WHERE
show_date='$row[show_date]' AND venue='$row[venue]');
var_dump($newquery); /* for example to see the query */
while ($row2 = mysql_fetch_array($newquery)) {
$num_rows = mysql_num_rows($row2);
if ($num_rows1) {
print etc etc (Do things with my $row2 data)
I get loads of:
Warning: Supplied argument is not a valid MySQL result resource in
/home/ufr2/underfoot-www/admin/dupeshows.html on line 51
Can you not reselect in another while while selecting? Does that make any
sense to anyone but me? I shouldent have to open another DB connection?
Anyhow... your I would appreciate any help.
-Jonathan
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL / PHP Database Help
Yes, you can re-select from the same database without opeing a new
connection.
I recommend that you print the contents od $newquery to verify it contains
the string you think it should. Perhaps the apostrophes you've enclosed the
$row[] variable are not expanding.
-Original Message-
From: Jonathan Underfoot [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 08, 2002 7:43 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] MySQL / PHP Database Help
I'm trying to write a script that checks for multiple entries on a table in
a database. So far I've been plagued by MySQL errors. I'm fairly confident
my scripting is ok (Then again, I've been wrong before.) I think moreover
there might be some difficulty with my theory. Then again, I could try and
do this with MySQL join statements. Any feedback would be appreciated.
$sqlquery = mysql_query(SELECT * FROM local_shows);
while ($row = mysql_fetch_array($sqlquery)) {
$newquery = mysql_query(SELECT * FROM local_shows WHERE
show_date='$row[show_date]' AND venue='$row[venue]');
while ($row2 = mysql_fetch_array($newquery)) {
$num_rows = mysql_num_rows($row2);
if ($num_rows1) {
print etc etc (Do things with my $row2 data)
I get loads of:
Warning: Supplied argument is not a valid MySQL result resource in
/home/ufr2/underfoot-www/admin/dupeshows.html on line 51
Can you not reselect in another while while selecting? Does that make any
sense to anyone but me? I shouldent have to open another DB connection?
Anyhow... your I would appreciate any help.
-Jonathan
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL / PHP Database Help
Jonathan, I'm trying to write a script that checks for multiple entries on a table in a database. So far I've been plagued by MySQL errors. I'm fairly confident my scripting is ok (Then again, I've been wrong before.) I think moreover there might be some difficulty with my theory. Then again, I could try and do this with MySQL join statements. Any feedback would be appreciated. ... Warning: Supplied argument is not a valid MySQL result resource in /home/ufr2/underfoot-www/admin/dupeshows.html on line 51 I notice that others have addressed your PHP/interfacing issues... You asked about theory. The current solution will require n+1 calls to the database (where there are n-rows in the local_shows table). That's quite 'expensive'. Which is likely to be the 'best tool for the job' - an external scripting language (PHP) or the RDBMS itself (MySQL)? Consider a 'MySQL-oriented' solution:- 1 if the two fields (show_date and venue) were made into a UNIQUE index, then the check would become superfluous because MySQL would ensure the situation never arose - at the cost of the time to perform the check/index insertion at every row INSERT or UPDATE (although you may gain a speed increase for certain SELECTS) 2 performing the existing PHP routine using SQL - you show only a list of 'duplicates' (and no DELETE), so: select show_date, venue, count(*) as freq from local_shows group by show_date, venue having freq1 will give you what you have attempted to code thus far. (you may wish/need to juggle the sequence of show_date and venue to suit) Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL / PHP Database Help
What does bool(false) mean?
Thats off the vardump
-J
- Original Message -
From: Jeroen Timmers [EMAIL PROTECTED]
To: Jonathan Underfoot [EMAIL PROTECTED]
Cc: [PHP-DB] [EMAIL PROTECTED]
Sent: Friday, February 08, 2002 8:58 AM
Subject: Re: [PHP-DB] MySQL / PHP Database Help
Hello,
something that you can help is the follow
try var_dump($newquery);
then you see the complete query and run it in phpmyadmin.
Jeroen
- Original Message -
From: Jonathan Underfoot [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, February 08, 2002 2:43 PM
Subject: [PHP-DB] MySQL / PHP Database Help
I'm trying to write a script that checks for multiple entries on a table
in
a database. So far I've been plagued by MySQL errors. I'm fairly
confident
my scripting is ok (Then again, I've been wrong before.) I think moreover
there might be some difficulty with my theory. Then again, I could try
and
do this with MySQL join statements. Any feedback would be appreciated.
$sqlquery = mysql_query(SELECT * FROM local_shows);
while ($row = mysql_fetch_array($sqlquery)) {
$newquery = mysql_query(SELECT * FROM local_shows WHERE
show_date='$row[show_date]' AND venue='$row[venue]');
var_dump($newquery); /* for example to see the query */
while ($row2 = mysql_fetch_array($newquery)) {
$num_rows = mysql_num_rows($row2);
if ($num_rows1) {
print etc etc (Do things with my $row2 data)
I get loads of:
Warning: Supplied argument is not a valid MySQL result resource in
/home/ufr2/underfoot-www/admin/dupeshows.html on line 51
Can you not reselect in another while while selecting? Does that make any
sense to anyone but me? I shouldent have to open another DB connection?
Anyhow... your I would appreciate any help.
-Jonathan
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL / PHP Database Help
You don't need to use var_dump. Just use the following statements:
$newquery = mysql_query(SELECT * FROM local_shows WHERE
show_date='$row[show_date]' AND venue='$row[venue]');
print $newquery;
-Original Message-
From: Jonathan Underfoot [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 08, 2002 8:48 AM
To: Jeroen Timmers
Cc: [PHP-DB]
Subject: Re: [PHP-DB] MySQL / PHP Database Help
What does bool(false) mean?
Thats off the vardump
-J
- Original Message -
From: Jeroen Timmers [EMAIL PROTECTED]
To: Jonathan Underfoot [EMAIL PROTECTED]
Cc: [PHP-DB] [EMAIL PROTECTED]
Sent: Friday, February 08, 2002 8:58 AM
Subject: Re: [PHP-DB] MySQL / PHP Database Help
Hello,
something that you can help is the follow
try var_dump($newquery);
then you see the complete query and run it in phpmyadmin.
Jeroen
- Original Message -
From: Jonathan Underfoot [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, February 08, 2002 2:43 PM
Subject: [PHP-DB] MySQL / PHP Database Help
I'm trying to write a script that checks for multiple entries on a table
in
a database. So far I've been plagued by MySQL errors. I'm fairly
confident
my scripting is ok (Then again, I've been wrong before.) I think moreover
there might be some difficulty with my theory. Then again, I could try
and
do this with MySQL join statements. Any feedback would be appreciated.
$sqlquery = mysql_query(SELECT * FROM local_shows);
while ($row = mysql_fetch_array($sqlquery)) {
$newquery = mysql_query(SELECT * FROM local_shows WHERE
show_date='$row[show_date]' AND venue='$row[venue]');
var_dump($newquery); /* for example to see the query */
while ($row2 = mysql_fetch_array($newquery)) {
$num_rows = mysql_num_rows($row2);
if ($num_rows1) {
print etc etc (Do things with my $row2 data)
I get loads of:
Warning: Supplied argument is not a valid MySQL result resource in
/home/ufr2/underfoot-www/admin/dupeshows.html on line 51
Can you not reselect in another while while selecting? Does that make any
sense to anyone but me? I shouldent have to open another DB connection?
Anyhow... your I would appreciate any help.
-Jonathan
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL / PHP Database Help
you need to print the qeury and i guess you print the result in the var_dump
i use always var_dump instead a print because the var_dump have more info.
jeroen
- Original Message -
From: Jonathan Underfoot [EMAIL PROTECTED]
To: Jeroen Timmers [EMAIL PROTECTED]
Cc: [PHP-DB] [EMAIL PROTECTED]
Sent: Friday, February 08, 2002 3:47 PM
Subject: Re: [PHP-DB] MySQL / PHP Database Help
What does bool(false) mean?
Thats off the vardump
-J
- Original Message -
From: Jeroen Timmers [EMAIL PROTECTED]
To: Jonathan Underfoot [EMAIL PROTECTED]
Cc: [PHP-DB] [EMAIL PROTECTED]
Sent: Friday, February 08, 2002 8:58 AM
Subject: Re: [PHP-DB] MySQL / PHP Database Help
Hello,
something that you can help is the follow
try var_dump($newquery);
then you see the complete query and run it in phpmyadmin.
Jeroen
- Original Message -
From: Jonathan Underfoot [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, February 08, 2002 2:43 PM
Subject: [PHP-DB] MySQL / PHP Database Help
I'm trying to write a script that checks for multiple entries on a table
in
a database. So far I've been plagued by MySQL errors. I'm fairly
confident
my scripting is ok (Then again, I've been wrong before.) I think
moreover
there might be some difficulty with my theory. Then again, I could try
and
do this with MySQL join statements. Any feedback would be appreciated.
$sqlquery = mysql_query(SELECT * FROM local_shows);
while ($row = mysql_fetch_array($sqlquery)) {
$newquery = mysql_query(SELECT * FROM local_shows WHERE
show_date='$row[show_date]' AND venue='$row[venue]');
var_dump($newquery); /* for example to see the query */
while ($row2 = mysql_fetch_array($newquery)) {
$num_rows = mysql_num_rows($row2);
if ($num_rows1) {
print etc etc (Do things with my $row2 data)
I get loads of:
Warning: Supplied argument is not a valid MySQL result resource in
/home/ufr2/underfoot-www/admin/dupeshows.html on line 51
Can you not reselect in another while while selecting? Does that make
any
sense to anyone but me? I shouldent have to open another DB connection?
Anyhow... your I would appreciate any help.
-Jonathan
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL / PHP Database Help
Jonathan, [have put the cc back to the list] Is it possible to make BOTH the date and venue into a single unique index? =Why not? Like a good woman, treat her right, and SQL will do almost anything for you: 6.5.3 CREATE TABLE Syntax CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement] create_definition: col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] [reference_definition] orPRIMARY KEY (index_col_name,...) orKEY [index_name] (index_col_name,...) orINDEX [index_name] (index_col_name,...) ... Note the ellipses (...) at the end of that last line - many people are used to writing [PRIMARY] KEY or INDEX immediately after field name and definition, forgetting that if it is a separate clause of the CREATE stmt, multiple columns may be specified! (Not that this works for me.) But I'm qcurious about this. I understand where this could be useful as a single unique index.. (as opposed to two unique indexes) Is this possible? How so? =yes it is possible, as above. =the short answer is: wherever you find yourself doing SELECT...WHERE field/index-condition1 AND field/index-condition2 =If only the first field/column is indexed, then obviously the SELECT will be faster than when accessing an unindexed table. However if there is a large fan-out between the two fields columns, (ie there are a large number of different values in field/column2 which share the same value in field/column1) then it may pay to combine the two fields into a single index for even faster results. Of course, the smaller your table, the harder it is to 'see' any return on the investment! (In my case multiple entries are ok, just as long as I can run a report to spot them, and then edit them which usually requires human interaction.) =If your system's data-entry stage is time-constrained then I would be tempted to agree. Otherwise conventional wisdom suggests that it is better to prevent 'dirty' data entering the system or data integrity issues creeping in, than it is to develop a strategy to 'clean' the db post-fact. Usually the person entering the data knows most about it - or has the best opportunity to ask the 'data source' for clarification! Your second suggestion worked rather well... although its not quite generating the output that would be best suited to me. The MySQL docs on Group By and Count are quite weak.. do you have something else you could send me / can you explain these commands. I was sure there is / was a way to do it in MySQL my SQL just isn't what it should be. =if you post the code you've developed thus far, and some sample source data and results, together with some specific criticism, we might be able to help with issues like best suited, or tweak the code I sent earlier to provide for situations that may not have been evident (at least to me) in your first post. =GROUP BY and COUNT() can be combined in many different ways, so what seems straightforward on the surface can yield enormous power when you start to tinker under the hood. I assume what you mean is that the manual is not really a tutorial. =Apart from the manual, I use books (I've picked up a few over the years - some probably now out of print; Paul DuBois' MySQL is current and the most specific - and has a PHP interface chapter, plus other more-PHP books, eg Welling Thomson) and there are a number of tutorial web sites either covering SQL generally or MySQL in particular (start at the MySQL site or any search engine). =Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
