MySQL Query help, what's wrong with this code?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
8ennett
Forum Commoner
Posts: 63
Joined: Sat Sep 06, 2008 7:05 am

MySQL Query help, what's wrong with this code?

Post by 8ennett »

###EDIT### Scratch this example, my next post is probably a better example! ###

Code: Select all

 
// Basic database query
function sql_query($query){
    return mysql_query($query);
}
 
// Create new user
function make_user($verifid, $mail, $user, $fname, $lname, $country, $pass, $thetime, $yourip){
    global $makeuser;
    $query = 'INSERT INTO memuserlist (verifid, user_email, user, handle, firstname, lastname, country, user_password, register_date, last_ip, last_access) VALUES ('$verifid', '$mail', '$user', '$user', '$fname', '$lname', '$country', PASSWORD('$pass'), '$thetime', '$yourip', '$thetime')';
    if(sql_query($query)){
        return true;
    }
    else{
        return false;
    }   
}
I can't get the query to insert the information into a new row, the database is currently blank but the table structure has been setup correctly!

Any help would be VERY greatly appreciated!

I tried to manually type the query in to the sql command line client replacing the $variables with the actual data i wanted but it still wouldn't work with no reason why.
Last edited by 8ennett on Sun Sep 07, 2008 11:44 am, edited 1 time in total.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: MySQL Query help, what's wrong with this code?

Post by Bill H »

Not much way to tell from what yu've given us. The only thing I can suggest is to put the column names in backticks in the SQL string. I don't think that will solve it, but it's worth a try.
User avatar
8ennett
Forum Commoner
Posts: 63
Joined: Sat Sep 06, 2008 7:05 am

Re: MySQL Query help, what's wrong with this code?

Post by 8ennett »

Right I can get it working in the MySQL CLC (forgot to add the ; to the end of the query), but not through PHP. Here's an example of some pointless code I wrote, could someone have a look at it and tell me where I went wrong so I can understand it a bit better?

Code: Select all

<?php
$conn = mysql_connect ('localhost', 'root', 'bennett') or die ('Cannot connect to the database because: ' . mysql_error());
mysql_select_db ('test');
$query = 'SELECT * FROM thetable WHERE name='Bennett'';
return mysql_query($query);
$row = mysql_fetch_array($query);
echo 'Start...';
echo $row[0];
echo $row[1];
echo $row[2];
echo '...end';
mysql_close('test');
 
?>
Those are not my real DB details just to let you know! I tried removing line 5 and modifying line 4 to;

Code: Select all

$query = mysql_query("SELECT * FROM thetable WHERE name='Bennett'");
but still no luck

All I am trying to do here is select a user from the database, put the data into an array in the order of the table and then output the users information one array at a time on to the web page. The table only contains 3 fields, id, name and password. When trying to run the PHP it results in just a blank page so something is wrong somewhere!
User avatar
8ennett
Forum Commoner
Posts: 63
Joined: Sat Sep 06, 2008 7:05 am

Re: MySQL Query help, what's wrong with this code?

Post by 8ennett »

Sorry to ask guys but this is REALLY frustrating me. I've tried re-writing the code differently and adding comments so i know exactly where each piece of code is up to but still it won't work!

Code: Select all

<?php
start_session();
// Define MySQL variables
$host = "localhost";
$user = "root";
$pass = "bennett";
$db = "test";
// Connect to MySQL
$connection = mysql_connect($host, $user, $pass);
// Connect to database
mysql_select_db($db) or die ("Unable to select database!");
// Set query
$query = "SELECT * FROM thetable WHERE name='Bennett';";
// Run query and store result in $result
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());
// Convert query in to an array
$row = mysql_fetch_array($result);
// Print each column of MySQL row individually
echo $row[0];
echo $row[1];
echo $row[2];
// Free up memory by removing result then close database connection
mysql_free_result($result);
mysql_close($connection);
 
?>
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: MySQL Query help, what's wrong with this code?

Post by andyhoneycutt »

If you aren't getting any errors (if you are, post them please) then your query is returning 0 results. Make sure, first, that your query is correct with concern to the data available in the destination table.

-Andy
User avatar
8ennett
Forum Commoner
Posts: 63
Joined: Sat Sep 06, 2008 7:05 am

Re: MySQL Query help, what's wrong with this code?

Post by 8ennett »

I run the EXACT same query through the MySQL Command Line Client and it returns the correct results in the correct order. Could it be that I'm missing a PHP extension? I have the php_mysql.dll and php_gd2.dll extensions installed, is that perhaps having an effect or haven't I included an extension in the php.ini file?

It doesn't turn out any results whatsoever, no errors which seems like there is something missing from the php and not the SQL query but I really can't see what it is.

## EDIT ## I found out i had mispelled extension=php_mysql.dll and so corrected it but no luck, then i realised i should have included php_mysqli.dll and not php_mysql.dll but still no luck. There is no problems with the extensions now as the GD2 library works perfectly for my captcha image.
User avatar
8ennett
Forum Commoner
Posts: 63
Joined: Sat Sep 06, 2008 7:05 am

Ok, close this topic now!

Post by 8ennett »

Found out where the problem is, MySQL isn't properly connected to PHP. Forgot about the libmysql.dll, This is a recent installation of apache, php and mysql after my old hdd conked out on me, I feel like such an idiot now lol!

## EDIT ## Had to re-enable php_mysql.dll extension aswell but it works perfectly now, not sure wether to implement php_mysqli.dll or not, would it make any noticeable difference? Sorry to clog up the forums!
Last edited by 8ennett on Mon Sep 08, 2008 7:17 pm, edited 2 times in total.
User avatar
8ennett
Forum Commoner
Posts: 63
Joined: Sat Sep 06, 2008 7:05 am

Re: MySQL Query help, what's wrong with this code?

Post by 8ennett »

Also, apart from me feeling like an idiot, I posted this on the general discussion forum but thought it would be appreciated the world over;

I only starting learning PHP about 3 days ago and am already capable of achieving so much, I just want to say thanks to the guys who have helped me in my queries.

I already have a good working knowledge of how to program code, arrays, loops, structures, terminology, and so on, and have created amazingly complex codes with very basic programming languages (such as D-Script (darksigns.com, a very complex game which implemented the first network environment within a game for the later systems and is always adapting and upgrading, current level is astounding with darksigns and darksignsonline), I managed to create an entire casino, with images expanding beyond the usual capability of the normal scripting language which is what programming is all about) but since my son was born 2 years ago (and I've had to take care of him since birth on my own :() I've always thought about learning PHP but never got around to it. So any posts I make and you reply to, thank you very much in helping me to learn and adapt and to any new methods I haven't encountered previously!

Thanks guys! That's why i chose this forum and not the others, you aren't condesending and actually help people!

;everyone loves praise and you guys deserve it!
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: MySQL Query help, what's wrong with this code?

Post by andyhoneycutt »

Thanks for reporting back your solution :) Glad you were able to get it solved.

-Andy
Post Reply