Jump to content

Archived

This topic is now archived and is closed to further replies.

lyndsey.pearce@ntlworld.com

Odd result when mixing SQL and PHP

Recommended Posts

Hello!

I am trying to create a web front end editing page to modify database entries.
All my Primary Key fields in the database end in hash for consistency and denote it contains a whole number.

I wrote script A:

[code]
$selectquery = "SELECT * FROM Page";
$result = mysql_query($selectquery)
or die("Invalid query: " . mysql_error());
while ($row = mysql_fetch_array($result)) {

<?php echo ">>"; echo $row['ColumnEntry']; ?>

        <a href="deletepage.php?type=TableName&id=<?php
                    echo $row['PrimaryKeyField#']?>">[DELETE]</a></p>
[/code]


When you click the link "DELETE" you get transfered to script B:

[code]
$sql = "DELETE FROM " . $_GET['type'] . "
            WHERE " . $_GET['type'] . "# = '" . $_GET['id'] . "'
            LIMIT 1";

            echo "$sql";
[/code]


Interestingly, the sql statement I echo out is:

[code]
DELETE FROM TableName WHERE PrimaryKeyField# = '1' LIMIT 1
[/code]

However, I get this error:

[code]
Invalid query: Unknown column 'PrimaryKeyField' in 'where clause'
[/code]

It misses out the hash. I have attached the hash symbol in the where clause like so:

[code]
WHERE " . $_GET['type'] . "# = '" . $_GET['id'] . "'
[/code]

And it is visible from the echo output.

What am I doing wrong, is it the way I mixed PHP and MySQL?
Is this even a PHP issue or a MySQL one?

Can anyone point out my mistake please?




Cheers!

Share this post


Link to post
Share on other sites
Well first thing i notice is that you are using $_GET['type'] in 2 different places but giving the same info.

Unless your table name is the same as your field name you are going to get an error. Cause acording to your parameters your sql is going to look like this.

[code]DELETE FROM TableName WHERE TableName# = '1' LIMIT 1[/code]

Might have to modify your code to pass the tablename, primarykeyfield and id. Not just tablename and id.

Or since you know the table name and field name just get the id

[code] DELETE FROM TableName WHERE PrimaryKeyField# = '".$_GET['id']."'";[/code]

Only way you need to do all the other stuff is if your list is coming from several tables, which it looks like it isn't

Ray


Share this post


Link to post
Share on other sites
Hey thanks alot for your help Ray!

I took your advice and got shot of the $_GET['type'] in two places and just used the name of the table and key field, as I am only querying 1 table.

I did notice something odd though. Something somewhere doesn't like the use of "#" in the name. I was using the same name for the PK field as for the table, except the key field is tablename#.

I can change this to anything else and the query works. Change it to something#, and I get an invalid query error.


Aah well!



Lyndsey

Share this post


Link to post
Share on other sites
If you are going to use that then enclose the field or table name in back ticks

`PrimaryKeyField#`

Ray

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.