PHP -> MySQL Problem

Code junkies hangout here

Moderators: ChrisThornett, LXF moderators

PHP -> MySQL Problem

Postby MGE » Tue Dec 05, 2006 2:12 am

Howdy all,

I am having a problem figuring out how to get data out of a MySQL database for a website I'm working on, sort of.
I say sort of, because this is a test page I'm working on to model the functions first and test them, before I put them on the actual page.

Anyway, I got the other functions I was working on done: Connecting, Creating a DB, Creating Tables, and inserting rows.
However, when it comes to getting the data back out of the database, I keep hitting a problem with mysql_fetch_array.

Here is the code I have for it so far:
Code: Select all
<?php
function selectDATA()
{
     mysql_select_db($_POST["SQLDB"],$GLOBALS['$con']);
     $result = mysql_query("SELECT * FROM " . $_POST["SQLTABLE"] . " WHERE " . $_POST["SQLFIELD"] . " = " . $_POST["SQLSEARCHDATA"]);
     $rez = "SELECT * FROM " . $_POST["SQLTABLE"] . " WHERE " . $_POST["SQLFIELD"] . " = " . $_POST["SQLSEARCHDATA"];
     echo $rez;
     while($row = mysql_fetch_array($result))
     {
          echo $row[$_POST["SQLFIELD"]];
          echo "<br />";
     }
}
?>


I fill in the form with the values, and when I click submit I get this error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/test/php/dbtest2.php on line 74

Line 74 = while($row = mysql_fetch_array($result))

The echo of the $rez var give's out proper SQL (as far as I am aware), E.g.
SELECT * FROM users WHERE UserName = TEST1

This has got me stumped, can someone please put me out of my misery and tell me what I'm doing wrong? Is it the PHP? The SQL?
Pentium 4 3.2Ghz Prescott 1MB Cache
Abit IC7 (875P Chipset)
3GB Corsair Dual Channel DDR
Nvidia GeForce 6600GT
500GB (SATA2-ReiserFS) Main drive
500GB (PATA-ReiserFS) Media drive
120GB (PATA-NTFS) Legacy data
OS: SimplyMEPIS 8
User avatar
MGE
 
Posts: 94
Joined: Thu Sep 08, 2005 2:53 am
Location: Manchester

RE: PHP -> MySQL Problem

Postby M0PHP » Tue Dec 05, 2006 8:33 am

The error is coming up because $result isn't a MySQL resource type, so the query is probably failing and returning false. You should really test for this first: http://uk2.php.net/mysql_query.

The problem with the query I think is the username you're looking for should be enclosed with single quotes, eg:

Code: Select all
$table = $_POST['SQLTABLE'];
$field = $_POST['SQLFIELD'];
$data = $_POST['SQLSEARCHDATA'];
$result = mysql_query("SELECT * FROM $table WHERE $field = '$data'");


Also, you are leaving yourself wide open to MySQL Injection attacks by dropping $_POST variables right into your query.
Image Image Image
User avatar
M0PHP
LXF regular
 
Posts: 737
Joined: Wed Apr 06, 2005 7:40 am
Location: Bishop Auckland, County Durham, UK

RE: PHP -> MySQL Problem

Postby filth » Sun Dec 17, 2006 11:33 am

as MOPHP stated you need to use mysql_query() on $rez and then fetch the results using mysql_fetch_array or some other function of your desire.

Also do not ever use user submitted information in sql queries. Before using anything make sure they are validated and are as expected. For example if something should be a number ensure it is a number. Also try using something like mysql_real_escape_string() on data in the query this will aid in combating sql injection (you should still do some validation even if you do use this, no point querying a database if the data is obviously wrong).

As well as using single quotes around inputted data it is also considered a good idea to use ` around column names so your sql would end up being:-

SELECT * FROM `users` WHERE UserName = 'TEST1 '
filth
 
Posts: 18
Joined: Sun Dec 17, 2006 11:25 am

RE: PHP -> MySQL Problem

Postby Hudzilla » Sun Dec 17, 2006 11:41 am

One other thing: rather than joining lots of strings together, you can just surround array names in braces, eg "SELECT * FROM users WHERE ID = '{$_POST["UserID"]}';" (note that the value is in quotes to avoid problems if people enter strings - just be sure you real_escape the variable if you have magic_quotes_gpc turned off!)
User avatar
Hudzilla
Site admin
 
Posts: 265
Joined: Mon Apr 04, 2005 11:52 am
Location: LXF Towers


Return to Programming

Who is online

Users browsing this forum: No registered users and 0 guests

cron