[RESOLVED (thanks)] Strange PHP/MYSQL problem?
I am having a really difficult time figuring out a problem I am having.
I am trying to help a friend with his website, and on one of the pages I need to add a record to a database.
I run the query with mysql
PHP Code:
print $query;
$result = mysql_query($query, $conn) or die(mysql_error());
and it dies without showing me what the problem was.
I figured it might have been my query, so I opened up the phpmyadmin for the webserver and pasted the exact query posted and it saves just fine.
I thought there might be a problem with my host/name/password or something so I copied over it with one from a query I know works and I get the same issue.
Has anyone encountered a problem where they could select data, but run into problems while adding? And, the page does NOT display any mysql_error() like it does with all the select statements if there is a problem. Is there a special thing I could put in the die() that actually will show me the error for an insert statement?
Thanks in advance
Re: Strange PHP/MYSQL problem?
Try
PHP Code:
$result = mysql_query($query, $conn) or die(" ".mysql_error());
Re: Strange PHP/MYSQL problem?
If error reporting isn't on, you'll get just a blank screen if their is any error, so maybe it's not your query, but something else you're missing on the page. Maybe post the code, and someone can have a closer look at it :)
Re: Strange PHP/MYSQL problem?
Well, mysql_error() shows an error for select statements, but I cannot get it to show anything for the insert statement. My guess is error reporting is on. I did however find WHERE the problem is but I have NO idea how to fix it....
I am calling a function in my query (which may not be right) to get the highest number in the table. I was doing some playing around, and if I hardcode the NextID() function to a number it saves just fine.
Since I saw that was where the problem is, I called the function into a variable above the query, and used the variable in the query. Still did the same thing. It only works if I hardcode a number where I need the function to be.
Any help would be appreciated. Here is my query and the function that causes the problem
PHP Code:
//before
$query = "INSERT INTO comments (post, id, name, email, url, subject, comment, dateposted, ip) ";
$query .= "VALUES(". $_GET['comment'] .", ". NextID() .", '".Fix($_POST['name'])."', '".Fix($_POST['email'])." ', '".Fix($_POST['url'])." ', '".Fix($_POST['subject'])."', '".Fix($_POST['message'])."', '" . date('Y-m-d') ."', '". $_SERVER['REMOTE_ADDR'] ."')";
function NextID()
{
$conn = mysql_connect('server', 'username', 'password');
mysql_select_db('database', $conn);
$query = "SELECT MAX(id) as maxID ";
$query .= "FROM comments ";
$query .= "WHERE post = ". $_GET['comment'];
$result = mysql_query($query, $conn) or die(mysql_Error());
$numRows = mysql_num_rows($result);
$nextID=1;
if($numRows>0)
{
$nextID = mysql_result($result,0,"maxID");
}
mysql_close($conn);
return $nextID;
}
Re: Strange PHP/MYSQL problem?
Is mysql_error() case sensitive? I don't recal, but it's worth a try, you have a capital 'E':
PHP Code:
$result = mysql_query($query, $conn) or die(mysql_Error());
I'm trying to understand why you're getting the maxID for, could you explain? :)
Good luck
Re: Strange PHP/MYSQL problem?
It isn't case sensitive, I am just bad with the shift key while typing. It works both ways (atleast on select statements).
Another update:
I tried to just insert it with a hard coded ID of 0 and then run an update statement on it, which in turn failed. Something is wrong with the function or how the value gets returned, I just can't figure it out.
Re: Strange PHP/MYSQL problem?
I still don't see why you are getting the max(id), what happends when you echo out just NextId(); somewhere on the page, does it return the correct value?
Re: Strange PHP/MYSQL problem?
is id auto_inc primary?
why not just stick it NULL (it will automatically +1).
i had a problem like this aswell... with a random pass generator.
i made the returned data into a global
ex: $rndpass = $key //($key being returned data)
then i call it in a global.
ex:
global $rndpass;
echo"Random Number: ".$rndpass;
Re: Strange PHP/MYSQL problem?
Yeah, the function works fine (for returning the correct value). I print it in the function it gets the correct value, I print it above my query before inserting it works ok, I (int)trim($var) it and it displays fine, but when I throw it in the query it craps out.
I had this exact same code on my home server machine and it works fine, but I am using php 4.3.3, and on the website they use php 4.3.10. I don't understand why that would cause a problem with a function, when other functions I have that do pretty much the same thing work fine. This only errors out if it is in a query of some sory.
Another thing, I found out the mysql_error() is working fine. I tried to hardcode the number 0 for ID (there is a unique constraint on post and id), and it was already inserted, so mysql_error() actually told me that was what the problem is. Now I am wondering if the mysql_query function sees the string as screwy so it just dies without running the query. I don't know php well enough to know how that works, but this problem is enough to make me stick with vb and c++. I am extremely confused.
Re: Strange PHP/MYSQL problem?
Re: Strange PHP/MYSQL problem?
Plague:
There might be a better way to do this, but I have it as a unique constraint with the post/id columns.
I need each post 1 to have incremental values starting at 1.
So post 1 wll have id's 1 2 3 4 5 and post 2 will have id's 1 2 3 4 5.
I need it formatted that way so I can use next/back functionality and only display a certain number of records at a time. I display the first 5 with
select field1, field2, field3, field4
from comments
where id between (number it is on) and (number + how many to show)
and post = (the post id for the certain page).
If the number is just an auto_increment it would possibly not show up at all, or show 1 record rather than how many I want. I saw that you can use limit 5 in the query to only pull back so mnay records, but have no idea how to use that correctly with a next/back functionality.
Re: Strange PHP/MYSQL problem?
have you checked out ... pagination?
PHPFREAKS::: PAGINATION TUTORIAL
my ways are different... dont listen to me ... if i am wrong...LOL
Re: Strange PHP/MYSQL problem?
Never heard of it. I am extremely new with php, or any webbased programming for that matter. I am only helping a friend out with it cause he doesn't understand the database backend part of things. How would I go about using pagination?
Edit:
I feel stupid, you gave me a link to check it out. Sorry, I am blind today. Thank you very much for all of your help I appreciate it.
Re: Strange PHP/MYSQL problem?
well... from what you told me...
You are trying to limit results, into pages sort of speak.
pagination is the whole <prev 1 2 3 4 5 next> thing
Re: Strange PHP/MYSQL problem?
Where are you connecting to the database outside of the function? If you already have a connection open, before you call this line:
PHP Code:
$query = "INSERT INTO comments (post, id, name, email, url, subject, comment, dateposted, ip) ";
$query .= "VALUES(". $_GET['comment'] .", ". NextID() .", '".Fix($_POST['name'])."', '".Fix($_POST['email'])." ', '".Fix($_POST['url'])." ', '".Fix($_POST['subject'])."', '".Fix($_POST['message'])."', '" . date('Y-m-d') ."', '". $_SERVER['REMOTE_ADDR'] ."')";
It won't work, becuase you are opening another connection when you call NextID();
Re: Strange PHP/MYSQL problem?
Haha, yeah that is what it was. I have a question for you yak, you might know. Does opening another connection screw up the first connection? Or is it when I use mysql_close() it closes both? It worked fine on my home machine webserver with php 4.3.3 but on the webserver my friend is using it is php 4.3.10 I am not sure if there was a change or what. Thank you a bunch though, I appreciate it a ton.