Odd result when mixing SQL and PHP

[lyndsey.pearce@ntlworld.com]

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!

[craygo]

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

[lyndsey.pearce@ntlworld.com]

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

[craygo]

If you are going to use that then enclose the field or table name in back ticks

`PrimaryKeyField#`

Ray