php sql "DROP TABLE" command

I'm relatively new at this. I can use php to create a 'products'
table in the SQL 'ximport' database using similar code as shown below
but with the create rather than the drop command line. However, I
cannot use the drop command to delete the table 'products'. What is
wrong with the following code saved in a file called drop.php
downloaded to my host Globat and run from the Explorer 6 browser with
the url -- drop.php:







***** BEGIN OF HTML PROGRAM *****

$conn = mysql_connect('localhost','nameximport','pwsdximport');
echo $conn;
mysql_select_db('ximport',$conn);
$sql = "DROP TABLE IF EXISTS 'products'";
$result = mysql_query($sql,$conn);
echo $result;
?>

***** END OF HTML PROGRAM *****





Note the drop line ends with single quote, double qoute, semicolon.
There is always a secret. I can't seen to figure it out. Maybe
Globat has not implemented this feature?! Thanks for any help. It
would be much appreciated.

Re: php sql "DROP TABLE" command

On Nov 13, 4:19 am, "John S." wrote:
> I'm relatively new at this. I can use php to create a 'products'
> table in the SQL 'ximport' database using similar code as shown below
> but with the create rather than the drop command line. However, I
> cannot use the drop command to delete the table 'products'. What is
> wrong with the following code saved in a file called drop.php
> downloaded to my host Globat and run from the Explorer 6 browser with
> the url -- drop.php:
>
>
>
>
>
>
>
***** BEGIN OF HTML PROGRAM *****

> > $conn = mysql_connect('localhost','nameximport','pwsdximport');
> echo $conn;
> mysql_select_db('ximport',$conn);
> $sql = "DROP TABLE IF EXISTS 'products'";
> $result = mysql_query($sql,$conn);
> echo $result;
> ?>
>
***** END OF HTML PROGRAM *****

>
>
>
> Note the drop line ends with single quote, double qoute, semicolon.
> There is always a secret. I can't seen to figure it out. Maybe
> Globat has not implemented this feature?! Thanks for any help. It
> would be much appreciated.

Hi John,
You need to put this single quote "`". The line will look like this

$sql = "DROP TABLE IF EXISTS `products`";

Also i recomend to exceute the query with the die statement (at least
at developing enviroment)

$result = mysql_query($sql,$conn) or die(mysql_error());

Hope it helps,

Ivan

Re: php sql "DROP TABLE" command

John S. wrote:

> $sql = "DROP TABLE IF EXISTS 'products'";
> $result = mysql_query($sql,$conn);

Single and double quote marks have very specific meanings in SQL. You want
to wrap the table name in double quotes, not single.

DROP TABLE "products"

However, MySQL has a nasty "feature" whereby is misinterprets the meaning
of double quotes. For this reason, it's probably nest to just leave them
out:

DROP TABLE products

As the quote marks are only really needed if the table name contains
certain non-alphanumeric characters.

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 7 days, 6 min.]

TrivialEncoder/0.2
http://tobyinkster.co.uk/blog/2007/08/19/trivial-encoder/

Re: php sql "DROP TABLE" command

Well guys,

Your suggestions did not seem to work. I tried leaving out the quotes
and I also tried changing the ' single quotes to ` single closing
quotes plus many other combinations. Could the database be write
protected or otherwise locked? I just want to be able to drop, add
and insert so I can auto populate a table with records I have already
entered into a dbf database file. Frustrating! Any other
suggestions, I have tried adjusting the sql user names and passwords,
but to no avail. Thanks for your input in advance.

Re: php sql "DROP TABLE" command

John S. wrote:
> I'm relatively new at this. I can use php to create a 'products'
> table in the SQL 'ximport' database using similar code as shown below
> but with the create rather than the drop command line. However, I
> cannot use the drop command to delete the table 'products'. What is
> wrong with the following code saved in a file called drop.php
> downloaded to my host Globat and run from the Explorer 6 browser with
> the url -- drop.php:
>
>
>
>
>
>
>
***** BEGIN OF HTML PROGRAM *****

> > $conn = mysql_connect('localhost','nameximport','pwsdximport');
> echo $conn;
> mysql_select_db('ximport',$conn);
> $sql = "DROP TABLE IF EXISTS 'products'";
> $result = mysql_query($sql,$conn);
> echo $result;
> ?>
>
***** END OF HTML PROGRAM *****

>
>
>
>
> Note the drop line ends with single quote, double qoute, semicolon.
> There is always a secret. I can't seen to figure it out. Maybe
> Globat has not implemented this feature?! Thanks for any help. It
> would be much appreciated.
>
>

OK, what's the return value in $result? If it's false, what's the
response from mysql_error()?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: php sql "DROP TABLE" command

Jerry,

I am now using the following code in drop.php,






***** BEGIN OF HTML PROGRAM *****


print "BEGINNING OF PHP
";
$conn = mysql_connect('localhost','ximport','ximport');
echo $conn;
mysql_select_db('ximport',$conn);
$sql = "DROP TABLE 'products'";
$result = mysql_query($sql,$conn) or die(mysql_error());
print "RESULT = ";
echo $result;
print "
END OF PHP";
?>


***** END OF HTML PROGRAM *****




If I put products in single quotes as 'products' in the DROP command,
I get the following output:

***** BEGIN OF HTML PROGRAM *****

BEGINNING OF PHP
Resource id #2You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near ''products'' at line 1

If I put products in back ticks as `products` in the DROP command, I
get the following output:

***** BEGIN OF HTML PROGRAM *****

BEGINNING OF PHP
Resource id #2RESULT = 1
END OF PHP

***** END OF HTML PROGRAM *****

OMG, it's gone. I am sure I tried that several times before. But I
can reproduce the operation, so something must have been hung up
somewhere.

If I put products with no quotes or ticks in the DROP command, I get
the following output:

***** BEGIN OF HTML PROGRAM *****

BEGINNING OF PHP
Resource id #2RESULT = 1
END OF PHP

***** END OF HTML PROGRAM *****


Seems to be better now. Can't figure it why it did not work
previously. Use single quote in the $conn command, back ticks
somewhere else, no quotes sometimes. Is there a definite rule for
using regular single quote, back tick (single opening quote), or no
quote for different commands or do some work and other don't? ` for
variable names, ' for real data, maybe?

Been testing for about 30 minutes now, and there must be some time
delay at my host because it doesn't always drop right away on 1st or
2nd refresh of drop.php (or create fast either), at least when I look
at the database via phpMyAdmin. Odd I think. But I think that that
might have been my problem, looking for instant verification and
instant gratification.

What's the difference between resource id #2 and id #21?

Thanks for your help. I think I am right track now, but this is new
stuff for a guy who understands the general concepts and who grew up
with Fortran, dbase and WordStar and doesn't appreciate Windows, etc.,
but loves the internet.

Regards.

Re: php sql "DROP TABLE" command

Good news. I can now auto populate my SQL database table from a dbase
III dbf file using PHP. Life is good. Thanks all.

Re: php sql "DROP TABLE" command

John S. wrote:
> Jerry,
>
> I am now using the following code in drop.php,
>
>
>
>
>
>
> ***** BEGIN OF HTML PROGRAM *****


> > print "BEGINNING OF PHP
";
> $conn = mysql_connect('localhost','ximport','ximport');
> echo $conn;
> mysql_select_db('ximport',$conn);
> $sql = "DROP TABLE 'products'";
> $result = mysql_query($sql,$conn) or die(mysql_error());
> print "RESULT = ";
> echo $result;
> print "
END OF PHP";
> ?>
>

***** END OF HTML PROGRAM *****
>
>
>
>
> If I put products in single quotes as 'products' in the DROP command,
> I get the following output:
>
> ***** BEGIN OF HTML PROGRAM *****
>
> BEGINNING OF PHP
> Resource id #2You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to
> use near ''products'' at line 1
>
> If I put products in back ticks as `products` in the DROP command, I
> get the following output:
>
> ***** BEGIN OF HTML PROGRAM *****
>
> BEGINNING OF PHP
> Resource id #2RESULT = 1
> END OF PHP
>
> ***** END OF HTML PROGRAM *****
>
> OMG, it's gone. I am sure I tried that several times before. But I
> can reproduce the operation, so something must have been hung up
> somewhere.
>
> If I put products with no quotes or ticks in the DROP command, I get
> the following output:
>
> ***** BEGIN OF HTML PROGRAM *****
>
> BEGINNING OF PHP
> Resource id #2RESULT = 1
> END OF PHP
>
> ***** END OF HTML PROGRAM *****
>
>
> Seems to be better now. Can't figure it why it did not work
> previously. Use single quote in the $conn command, back ticks
> somewhere else, no quotes sometimes. Is there a definite rule for
> using regular single quote, back tick (single opening quote), or no
> quote for different commands or do some work and other don't? ` for
> variable names, ' for real data, maybe?
>
> Been testing for about 30 minutes now, and there must be some time
> delay at my host because it doesn't always drop right away on 1st or
> 2nd refresh of drop.php (or create fast either), at least when I look
> at the database via phpMyAdmin. Odd I think. But I think that that
> might have been my problem, looking for instant verification and
> instant gratification.
>
> What's the difference between resource id #2 and id #21?
>
> Thanks for your help. I think I am right track now, but this is new
> stuff for a guy who understands the general concepts and who grew up
> with Fortran, dbase and WordStar and doesn't appreciate Windows, etc.,
> but loves the internet.
>
> Regards.
>
>

That is correct. Table and column names should not be within single
quotes. In fact, there is no reason to use backtickies either, unless
you're using a reserved word for a table or column name. And that's not
a good idea.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: php sql "DROP TABLE" command

"Toby A Inkster" wrote in message
news:rf2p05-nrb.ln1@ophelia.g5n.co.uk...
> John S. wrote:
>
>> $sql = "DROP TABLE IF EXISTS 'products'";
>> $result = mysql_query($sql,$conn);
>
> Single and double quote marks have very specific meanings in SQL. You want
> to wrap the table name in double quotes, not single.

I was under the impression that square braces were the way to go:
$sSQL = "DROP TABLE IF EXISTS [products]";






>
> DROP TABLE "products"
>
> However, MySQL has a nasty "feature" whereby is misinterprets the meaning
> of double quotes. For this reason, it's probably nest to just leave them
> out:
>
> DROP TABLE products
>
> As the quote marks are only really needed if the table name contains
> certain non-alphanumeric characters.
>
> --
> Toby A Inkster BSc (Hons) ARCS
> [Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
> [OS: Linux 2.6.12-12mdksmp, up 7 days, 6 min.]
>
> TrivialEncoder/0.2
> http://tobyinkster.co.uk/blog/2007/08/19/trivial-encoder/

Re: php sql "DROP TABLE" command

"Sanders Kaufman" wrote:
>"Toby A Inkster" wrote:
>> John S. wrote:
>>
>>> $sql = "DROP TABLE IF EXISTS 'products'";
>>> $result = mysql_query($sql,$conn);
>>
>> Single and double quote marks have very specific meanings in SQL. You want
>> to wrap the table name in double quotes, not single.
>
>I was under the impression that square braces were the way to go:
>$sSQL = "DROP TABLE IF EXISTS [products]";

No, that's a Microsoft extension that only works in Access. It isn't SQL.
--
Tim Roberts, timr@probo.com
Providenza & Boekelheide, Inc.

Re: php sql "DROP TABLE" command

Tim Roberts wrote:
> Sanders Kaufman wrote:
>
>> I was under the impression that square braces were the way to go:
>> $sSQL = "DROP TABLE IF EXISTS [products]";
>
> No, that's a Microsoft extension that only works in Access. It isn't SQL.

And Microsoft SQL Server since, IIRC, version 7... or whenever it was they
started to really diverge from the Sybase code. (Microsoft SQL Server was
originally a re-badged version of Sybase SQL Server.)

The SQL-compliant way of quoting table names is to use double-quotes. But
since the OP was using MySQL, which doesn't support the SQL-compliant
method (not by default anyway), that wouldn't work.

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 8 days, 19:40.]

TrivialEncoder/0.2
http://tobyinkster.co.uk/blog/2007/08/19/trivial-encoder/