[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

Try

PHP Code:

---

$result = mysql_query($query, $conn) or die(" ".mysql_error()); 


---

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 :)

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;
} 


---

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

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.

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?

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;

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.

read above post.^^^^^

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.

have you checked out ... pagination?
PHPFREAKS::: PAGINATION TUTORIAL

my ways are different... dont listen to me ... if i am wrong...LOL

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.

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

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();

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.