Linux Format forums Forum Index Linux Format forums
Help, discussion, magazine feedback and more
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

PHP -> MySQL Problem

 
Post new topic   Reply to topic    Linux Format forums Forum Index -> Programming
View previous topic :: View next topic  
Author Message
MGE



Joined: Thu Sep 08, 2005 3:53 am
Posts: 94
Location: Manchester

PostPosted: Tue Dec 05, 2006 2:12 am    Post subject: PHP -> MySQL Problem Reply with quote

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:
<?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
Back to top
View user's profile Send private message
M0PHP
LXF regular


Joined: Wed Apr 06, 2005 8:40 am
Posts: 737
Location: Bishop Auckland, County Durham, UK

PostPosted: Tue Dec 05, 2006 8:33 am    Post subject: RE: PHP -> MySQL Problem Reply with quote

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:

$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.
_________________
Back to top
View user's profile Send private message Visit poster's website
filth



Joined: Sun Dec 17, 2006 11:25 am
Posts: 18

PostPosted: Sun Dec 17, 2006 11:33 am    Post subject: RE: PHP -> MySQL Problem Reply with quote

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 '
Back to top
View user's profile Send private message
Hudzilla
Site admin


Joined: Mon Apr 04, 2005 12:52 pm
Posts: 265
Location: LXF Towers

PostPosted: Sun Dec 17, 2006 11:41 am    Post subject: RE: PHP -> MySQL Problem Reply with quote

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!)
Back to top
View user's profile Send private message Visit poster's website
View previous topic :: View next topic  
Display posts from previous:   
Post new topic   Reply to topic    Linux Format forums Forum Index -> Programming All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Linux Format forums topic RSS feed 


Powered by phpBB © 2001, 2005 phpBB Group


Copyright 2011 Future Publishing, all rights reserved.


Web hosting by UKFast