0

I set up a new MySQL database and created some PHP Web pages. The IDs for each entry are composed of three digits and have leading zeros (e.g., 000, 001, 002).

My main page that shows every ID as a separate row in an HTML table works fine -- it displays every entry. But my individual entry page is not returning specific entries. For example, the URL entry.php?id=001 and entry.php?id=002 returns the entry for every ID.

I believe the error is at the beginning of the entry.php code, which looks like this: 

$query = 'SELECT * FROM databasetablename';
if (isset($_GET['id']) && is_numeric($_GET['id']))
{
    $query .= ' WHERE id = ' . (int)$_GET['id'];  
}
$result = mysql_query($query);  

while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

... and the code goes on. But I think the error is in this part.

Kirk Beard
  • 9,569
  • 12
  • 43
  • 47
N. Scribner
  • 43
  • 1
  • 8
  • 2
    better you use mysqli or pdo instead of mysql.mysql is deprecated.https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php – Shanu k k May 31 '17 at 04:56
  • what is the datatype of your `id` field in table? – Al Amin Chayan May 31 '17 at 05:09
  • Datatype for ID is: int(3) unsigned zerofill. Does that look right? I think I got that from Stack Overflow. – N. Scribner May 31 '17 at 05:12
  • @N. Scribner you can use `str_pad($id,$digits,'0',STR_PAD_LEFT)` – Nikhil Radadiya May 31 '17 at 05:13
  • NOTE: It turns out this entry page is actually displaying every entry, not just 000. Yet it works fine with another database that doesn't use leading zeros. Any ideas? – N. Scribner May 31 '17 at 05:17
  • I really appreciate all the quick help. But I need to let everyone know I made an error in this post. The entry.php displays EVERY ENTRY -- not just ID 000. – N. Scribner May 31 '17 at 05:23
  • Re: deprecated code. I am a beginner coder, so I'm not really sure how to fix it. – N. Scribner May 31 '17 at 05:34
  • Actually, your code **should** work as is. What is contents of `$query`? Normally php would return `true` for `is_numeric('001')` but I'm assuming your conditional is not being met, resulting in only the first part of the query getting executed. Possible because `$_GET['id']` is not actually set here. – But those new buttons though.. May 31 '17 at 06:01
  • Thanks for the comment. Do you know how to fix the code? – N. Scribner May 31 '17 at 06:05
  • @N.Scribner - If you answer my question I might be able to. – But those new buttons though.. May 31 '17 at 06:14
  • Hi, I'm not sure how to answer your question. The contents of $query, I believe, are just what I have posted. – N. Scribner May 31 '17 at 14:58
  • Still no correct answers. I would really appreciate it if someone could answer this quickly! – N. Scribner May 31 '17 at 17:03
  • I'm very sorry for responding to this thread late with the solution. As stupid as it sounds, I believe the issue was that the URLs did not have the "id" part of the URL set to a number. The code was fine; it was just the links that were causing the problems. – N. Scribner Jul 05 '17 at 09:47

2 Answers2

0

You are casting $_GET['id'] as int after which $_GET['id'] is now = 1

sudo
  • 323
  • 3
  • 12
0

I assume page IDs in database are not of a numeric type, but of CHAR/VARCHAR/TEXT. In this case you should try this:

$query = 'SELECT * FROM databasetablename';
if (isset($_GET['id']) && is_numeric($_GET['id']))
{
    $query .= ' WHERE CAST(id AS INTEGER) = ' . (int)$_GET['id'];  
}
$result = mysql_query($query);

As a side note: consider using PDO with parameter binding instead of building queries directly from request parameters. This would be extremely dangerous:

$query = ' WHERE CAST(id AS INTEGER) = ' . $_GET['id'];

EDIT: You could also try using mysql_real_escape_string():

$query .= ' WHERE id = ' . mysql_real_escape_string($_GET['id']);

but in this case you should read the security warning about character sets here: http://php.net/manual/en/function.mysql-real-escape-string.php

EDIT2: Sorry, I cannot write comments at the moment, but since you said that it returns every entry it could only mean that the WHERE condition is not added. Check if you actually receive the "id" request parameter in entry.php by using var_dump($_GET).

Viacheslav
  • 84
  • 1
  • 5
  • The "id" type is actually the following (not sure if it's right): int(3) unsigned zerofill – N. Scribner May 31 '17 at 05:32
  • I'm not sure how to use var_dump($_GET), but I did an echo with it and it returned the following: array(0) {}. If you look at my above code the WHERE condition should be added. As I said, this script works with other databases I use. I think the problem is the leading zeros. – N. Scribner May 31 '17 at 16:37
  • @N.Scribner `array(0) {}` means that $_GET is empty, which indicates that `isset($_GET['id'])` returns false. That is why WHERE condition is not being added. As for the reason why id parameter is missing: there may be multiple. Possibility is that you have a redirect in `.htaccess`, which redirects to `entry.php` without passing parameters. Or maybe `entry.php` has some code that is removing id from $_GET. – Viacheslav Jun 01 '17 at 02:48
  • @N.Scribner If you have a RewriteRule in `.htaccess` that redirects to entry.php, then make sure it has [QSA] flag. Overall it is very difficult to tell what is the real reason without seeing the whole context. – Viacheslav Jun 01 '17 at 03:02
  • @N.Scribner I just also remembered another thing with `.htaccess`. It is a common mistake when developers add a RewriteRule so that if host name does not start with www it redirects to same address with www but forget to add [QSA] flag. This would result in `http://example.com/entry.php?id=001` to be redirected to `http://www.example.com/entry.php`. Or this may even be made by server itself depending on how hosting provider has it configured, but I really doubt that. – Viacheslav Jun 01 '17 at 03:08