Page 1 of 1

[SOLVED] Select works, Insert, delete and update don't

Posted: Mon Feb 09, 2004 3:49 pm
by JoeyCA
I have multiple webpages using PHP that do specific funtions, ie. look at registered members, update member info, delete them and move new members to a registered members database, so they will be able to log in.

I have an INSERT.. SELECT statement that works fine at the Mysql command line, but returns nothing when run from the web page. The page just flashes (like it is doing something), and fails to do the insert.

I did put in a bit of code that would perform a select on the new users database and another that would select from the registered members database and they both work fine (I have since removed them), but cannot get any insert, update or delete to work.

I know that it must be a configuration problem in mysql, but don't know where to go to fix it.

I created duplicate databases on a linux machine here and the pages work just fine, but as I said, they fail on the production machine.

Here is the code:

Code: Select all

<?php
include('../includes/config.inc');

$page_title = "Move New Users to Registered User DB";
include('../includes/admin_header.html');

require_once ('../../authentication.php');
   if (isset($_SESSION&#1111;'username'])) &#123;
     if ($authorized) &#123;
         include('../../mysql_connect.php');
      echo '<h1>Copy New User to Registered User DB</h1><br />';
	  
	 if (isset($submit)) &#123;
	  
	     $query="INSERT INTO users (username, realname, level, email, msn, aol, registration_date, player_type, password) SELECT username, realname, level, email, msn, aol, registration_date, player_type, password FROM newusers where username='$user_name'";
		 $result=mysql_query($query);
		 
	  if ($result) &#123;
	  
	      echo 'New Member Moved to Registered Users Database';
		  
	   &#125; else &#123;
	   
	      echo "Insert failed for New User";
	   &#125;
	  &#125;
	 
?>

<form action="<?php ($_SERVER&#1111;'PHP_SELF']); ?>" method="post">
<fieldset>
USER NAME:<input name="user_name" type="text" /><br />
</fieldset>
<div align="center"><input type="submit" name="submit" value="MOVE USER" /></div>
</form>

<?php
   &#125;
&#125;
include('../includes/admin_footer.html');
?>
The SESSION and authentication work fine, and since I am able to do SELECT statements, I know thet my mysql_connect file works.

Any Ideas??

Select works, Insert, delete and update don't

Posted: Mon Feb 09, 2004 4:08 pm
by JoeyCA
I went through the code adding 'or die' statements, again, nothing gets returned.

Re: Select works, Insert, delete and update don't

Posted: Mon Feb 09, 2004 5:40 pm
by Dr Evil
JoeyCA wrote:I know that it must be a configuration problem in mysql, but don't know where to go to fix it.

I created duplicate databases on a linux machine here and the pages work just fine, but as I said, they fail on the production machine.
You could be right. Does the user/accessyou are using with the production MySQL have all rights on the database or can it only read? Locally you possibly have root rights and on the prod server less rights.

Check that first!

Re: Select works, Insert, delete and update don't

Posted: Mon Feb 09, 2004 5:48 pm
by JoeyCA
I decided to reinstall MySQL on the server. It is a production server, but this site I am building is the only one that is database driven, and it isn't 'in production' yet.

I was connecting to the database as root.. I should have had all privs. that is where I was going nuts. I have completed the reinstall and will populate the database with my data when I get home. then try again.

Thanks for the reply!! :D

Re: Select works, Insert, delete and update don't

Posted: Tue Feb 10, 2004 11:55 am
by JoeyCA
Ok.. I reinstalled mysql, configured it and checked the configuration of PHP (MySQL 4.0.17, PHP 4.3.2). Everything looks good.

I can perform insers, deletes, updates and selects from the command line, but am still unable to 'write' to the database from my php web page.

Any more ideas?

TIA,

Joe

Posted: Tue Feb 10, 2004 4:53 pm
by Dr Evil
Sorry I will need a bit more time to go through your problem.
Just one question why do you have two tables ?
It seems more logical to add a new field with a simple 0 or 1 flag for users or registered users and stock it all in one table.
You'll save masses of time !

Dr Evil

Re: Select works, Insert, delete and update don't

Posted: Tue Feb 10, 2004 5:05 pm
by JoeyCA
You ask a good question about the two tables. I had thought about doing something like that, but I just copied a design I used on a page at work, modified it for the son's 'Clan' site. This is his page I am having the trouble with.

Still with one table, I can't update the 0 to a 1 with the problem I am having.

I thought about just putting this site on the server at work for a while (using a redirect), until I figure this out, but the amount of traffic that is generated would slow it all down at work. So that is out of the question.

If you can think of anything, please let me know.. I am working on this a couple of hours at night, experimenting and even a few hours a day at work. :D

Posted: Wed Feb 11, 2004 3:35 am
by Wayne
are you sure the user permissions are correct, connecting as ROOT from the local machine may have different permissions then connecting as ROOT from either a different server or by connecting by IP address instead of using localhost on the same server.

Posted: Wed Feb 11, 2004 8:40 am
by Illusionist
I remember having similar problems when using ROOT... try creating a new user for the database and assigning a password and give it right. Then use that user and see if it works. OR if you've already tried that, then i'm out of ideas!

Re: Select works, Insert, delete and update don't

Posted: Wed Feb 11, 2004 4:56 pm
by JoeyCA
Ok.. This one is SOLVED.

It took a long while and a lot of compiling, but it works.

The fix.. Created a new user in mysql with rights to the database and tables it was going to access.. it didn't solve the problem.

I then decided to update PHP incrementally.. The original build on my server was 4.0.6. I downloaded and installed 4.1.2, then 4.2.3 then 4.3.4. Trying it after each build. Finally!

I think I am going to go to the single table idea and just add a column to the table to make them registered or not registered.

Thanks for all the assistance and ideas! It has been great!

Joe

[SOLVED] Select works, Insert, delete and update don't

Posted: Thu Feb 12, 2004 10:08 am
by JoeyCA
Thanks!