I'm a newb at web programming. Ugh, this is making me crazy! I've been programming it all day.
I need expert's help. Please help me guys!!

I've been trying to make a Sign Up filter and insert to database code. The code first checks if the username inputted in the sign up exists in the database. If it does, it will ignore the sign up transaction and goes back to index.php. If it doesn't it will store all the inputs to the database.
After inputting, I'm trying to get the idNum (primary key, auto increment) from table 'user' of the newly inputted record and put it in visitNum (primary key for table visit but not auto increment. Table visit is very dependent to table user). But as you can see, I don't know how.

I'm using phpdev423

I have very many problems.
1. First time I sign up, the record is successfully registered in the database. After the first sign up, it won't work anymore. I input a record, it will display "Sign Up Complete" but I don't see anything in the phpmyadmin database. And when I input the same username, it will recognize the username and it will prevent me from registering the record. But when I look at the database, the record isn't there. I tried inputting a record in phpmyadmin manually. It will work and sign up will work again.

2. I already have 4 records in the database. The idNum are 101, 102, 103 and 104. I inputted my 4th record manually. When I input my fifth record from the sign up page, this thing is displayed: Error: Duplicate entry '103' for key 1. I don't understand, isn't idNum supposed to be at 105? My idNum is autoincrement, it's not supposed to be 103.

3. I don't know how to retrieve idNum without using mysql_fetch_array. How do I retrieve an idNum without using mysql_fetch_array.

So, here's my code.

<html>
  <head>
  </head>
  
  <body>
<?php
  $user = $_POST["USERname"];
  
  $con = mysql_connect();
  
  mysql_select_db("xxx", $con);
  
  $userz = (mysql_query("SELECT (username) FROM user"));
  
  while($row = mysql_fetch_array($userz))
     if($row["username"] == $user)
     {
	   mysql_close($con);
	   ?>
	   <script type="text/javascript">
	   alert("Username already exist. Please choose another one");
           window.location="index.php";
	   </script>
	   <?php
	}
	
	
 $sql="INSERT INTO user (fname, lname, age, gender, username, password, DateJoined) VALUES ('$_POST[FNname]','$_POST[LNname]','$_POST[AGEname]','$_POST[gender]','$_POST[USERname]','$_POST[PASSname]',curdate())";

  if (!mysql_query($sql,$con))
  {
    die('Error: ' . mysql_error());
  }
  
  mysql_close($con);
	
	$con2 = mysql_connect();
	
	mysql_select_db("worldsee", $con2);
  $ext = mysql_query("SELECT * FROM user WHERE fname='$_POST[FNname]'");

  $sequel="INSERT INTO visit (visitNum) VALUES ('$ext')";
  
  if (!mysql_query($sequel,$con2))
  {
    die('Error: ' . mysql_error());
  }
  mysql_close($con2);
  
  ?>
  <script type="text/javascript">
    alert("Sign Up Complete!");
	window.location="index.php";
  </script>

  </body>
  
 </html>
Member Avatar for diafol
<html>
  <head>
  </head>
  
  <body>
<?php
if(isset($_POST)){
  $user = $_POST["USERname"];
  $con = mysql_connect();  //As a rule you'd hold this in an include file so you don't have to do this every time
  mysql_select_db("xxx", $con); //same goes for this
  
  $userz = mysql_query("SELECT username FROM user WHERE username='$user'");
  if(mysql_num_rows($userz)>0){
     //show that this exists
  }else{
     $sql=mysql_query("INSERT INTO user (fname, lname, age, gender, username, password, DateJoined) VALUES ('$_POST[FNname]','$_POST[LNname]','$_POST[AGEname]','$_POST[gender]','$_POST[USERname]','$_POST[PASSname]',curdate()))";
     //I assume this is a different DB or even a different host to the last
     $con2 = mysql_connect(); //details for this the same as above?? If so no need to repeat
     mysql_select_db("worldsee", $con2); //if this really is a different DB, fine, otherwise just leave out
     $ext = mysql_query("SELECT * FROM user WHERE fname='$_POST[FNname]'");
     //the above will not extract the value you're looking for - you need to mysql_fetch_array first. SO the statement below WON'T work
    // $sequel=mysql_query("INSERT INTO visit (visitNum) VALUES ('$ext'))";
  }
}  
?>
  </body>
</html>

I've stripped the error functionality and js just for clarity. I'd use a different approach for the js though.

Oops. I forgot to change "worldsee" to "xxx". my bad. Everything is in one database.
Anyway, Thanks. Im such a noob programmer. :) I'm gonna try that.

Problems 1 and 2 are solved. My problem is problem number 3. If I insert a new record, it will register in the database. I want to store the idNum of the newly added record to visitNum. But I get this: Could not connect: Query was empty. Why??? I'm so confused. Please help me guys.

<html>
  <head>
  </head>
  
  <body>
  <?php
  $user = $_POST['USERname'];
  
  $con = mysql_connect();
  
   if(!$con)
   die('Could not connect: ' . mysql_error());
   
  mysql_select_db("worldsee", $con);
  
  $userz = (mysql_query("SELECT username FROM user WHERE username='$user'"));
  
  if(mysql_num_rows($userz) > 0) {
     mysql_close($con);
	 ?>
	 <script type="text/javascript">
	   alert("Username already exist");
	   window.location="index.php";
	 </script>
  <?php
  }
  else {
    $sql=("INSERT INTO user (fname, lname, age, gender, username, password, DateJoined) VALUES ('$_POST[FNname]','$_POST[LNname]','$_POST[AGEname]','$_POST[gender]','$_POST[USERname]','$_POST[PASSname]',curdate())");
    
	if(!mysql_query($sql, $con))
	  die('Could not connect: ' . mysql_error());
 }
  
  $num = mysql_fetch_assoc($userz);
  
  $sql=mysql_query("INSERT INTO visit (visitNum) VALUE('$num[idNum]')");
  if(!mysql_query($sql, $con))
	  die('Could not connect: ' . mysql_error());
  
  mysql_close($con);
  
 ?>  
  <script type="text/javascript">
       alert("Sign Up Completed!");
	   window.location="index.php";
	 </script>
  </body>
  
 </html>
Member Avatar for TechySafi

Look its your query

$userz = (mysql_query("SELECT username FROM user WHERE username='$user'"));

You selected only username, not other columns.Thats why when you used this

$num = mysql_fetch_assoc($userz);
 
  $sql=mysql_query("INSERT INTO visit (visitNum) VALUE('$num[idNum]')");

mysql_fetch_assoc($userz) didn't get any values for $num[idNum]. So I would suggest you to use a query something like

$userz2 = (mysql_query("SELECT username,idNum FROM user WHERE username='$user'"));
$num = mysql_fetch_assoc($userz);
 $sql=mysql_query("INSERT INTO visit (visitNum) VALUE('$num[idNum]')");

Let me know if it works now.

^^ thanks man. It helped me a lot.
It's working now. It took me time though. The real field in user table was userId not idNum.
idNum was from another project I made.

Gosh, I'm so dumb. It took me 2 hours to figure it out. :(

Member Avatar for TechySafi

Haha you are welcome!

still you should consider yourself lucky :P When I was working with twitter api, it took me whole night to figure out the stdclass object and echoing what I want to echo :D

Cheers keep going!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.