Auto-Inc, Inserting data, retrieving data, Random numbers

Anvicuth

ok. I have a double problem. I've been reading and reading online and I'm rather stuck, and the answer is likely right under my nose.

First thing is I need to insert a random number into a column on a table.

I'm guessing I must first use this:

rand(0,100);

However, the actual 'insert into field" etc... part is where I'm a wee bit stuck.

The second problem is this:

In this database, the table has information inserted. One of the fields not inserted (Auto-filled in by Auto_increment) will hold a number that I need to echo to the webpage so that the person who just sent the info to the table has it and can write it down.

This part, I'm not QUITE sure where to start. I'm not asking for a full out script, but maybe a few ideas or hints of where to start looking.

If you need to look at the entire script, let me know. And thank you ahead of time!

As well, is there ANY way to reset Auto_increment?

PS - I know how to insert data, however, getting the random nmber to insert is the trick I'm stuck with, not the actual insert code.

Pig

1) use rand to assign the number to a variable, and then use the var in your query.
$num = rand(0, 100);
$result = mysql_query("INSERT INTO table (field) VALUES ($num)");

2) to get the inserted ID, use mysql_insert_id().

eoghain

Well as for your random number issue there are 2 things you can do.

generate the random number in PHP store it in a variable and use that to insert into the database.
```
$rand = rand(0,100);
$sql = "INSERT INTO MyTable (randomValue) VALUES ($rand)";
```
use a database function to generate the random value for you (example for MySQL)
```
$sql = "INSERT INTO MyTable (randomValue) VALUES ((RAND() * 100))
```
You need to * by 100 when using the MySQL function because it returns a value from 0 to 1.0.

No as for auto increment fields you can get the value back out using another database function or typically there will be a function in php that does this for you.

Using just the database (MySQL again)

// Do original insert query here
$sql = "SELECT LAST_INSERT_ID()";

Using PHP builtin functions

// Do original insert query here
$newID = mysql_insert_id();

As for resetting auto increment values the only way I know of is to drop and re-create you table. I'm sure there is another way to do it but I can't think of why anyone would want to.

Anvicuth

Hmm. Okay thanks!

as for the mysql_insert_id, the exact name of the field is owner_id... would that affect it?

eoghain

The only thing that would affect it, is if you have more than 1 auto increment field in your table (but that's not a recommended situation, and I can't even think of a situation where I'd need more than 1).

Anvicuth

blinks

Okay, may be I dont know what you mean on how to use it...

What about this?

function getUserid( $email ) {
     $sql = "select userid from user where email = $email";
     $dbobj = mysql_query( $sql );
     $dbarray = mysql_fetch_array( $dbobj );
     return $dbarray[0];

then, I have it this further down:

<p>Thank you <b><? echo $uname; ?></b>, your information has been added to the database, you may now <a href="main.php" title="Login">log in</a> using your User Number, which is <?= getUser_id( $email ); ?>. Please write this down as you must have it to log in.</p>

I admit I'm a bit lost how I will modify it for the new query.

Anvicuth

Ok... the random doesn't seem to work.

No error pertaining to it comes up, here's the code:


function randomluck($email){
$rand = rand(0,100); 
$sql = "UPDATE users SET luck = $rand where email='$email'";
}

However, an error coming up pertaining to the "get owner_id" bit did.... I'm gonna try the other way wiuth the mysql id thing... still puzzled on how that will know what to fetch, but I'll try it.

Anvicuth

Hmm... ok I narrowed down the problem I think with the user ID code.

function getUserid( $email ) {
     $sql = "select owner_id from user where email = $email";
     $dbobj = mysql_query( $sql );
     $dbarray = mysql_fetch_array( $dbobj );
     return $dbarray[0];

And this is the error:

Warning: Supplied argument is not a valid MySQL result resource in /home/greydawn/www/register.php on line 39

Anvicuth

Hmm... ok I narrowed down the problem I think with the user ID code.

function getUserid( $email ) {
     $sql = "select owner_id from user where email = $email";
     $dbobj = mysql_query( $sql );
     $dbarray = mysql_fetch_array( $dbobj );
     return $dbarray[0];

And this is the error:

Warning: Supplied argument is not a valid MySQL result resource in /home/greydawn/www/register.php on line 39

Line 39 is the one with return $dbarray[0]; on it. Any ideas?

eoghain

Well the error that you are getting says that you don't have a valid result, so is sounds like your query is failing in some way. Change your code to this and see what it says.

function getUserid( $email ) { 
     $sql = "select owner_id from user where email = $email"; 
     $dbobj = mysql_query( $sql ) or  die ("Problem with SQL: $sql"); 
     $dbarray = mysql_fetch_array( $dbobj ); 
     return $dbarray[0];
}

Roger_Ramjet

Originally posted by Anvicuth

function getUserid( $email ) {
     $sql = "select owner_id from user where email = $email";
     $dbobj = mysql_query( $sql );
     $dbarray = mysql_fetch_array( $dbobj );
     return $dbarray[0];

[/B]

Your query will not work because $email will be treated as a column name not a value. Put quotes around it for the query to work:

$sql = "select owner_id from user where email = ' " . $email . " ' ";

Your query would then treat $email as a string value.

eoghain

Good catch Roger, I didn't even see it.

Anvicuth

Well I think we're getting closer.

Thank you Rock, your information has been added to the database, you may now log in using your User Number, which is Problem with SQL: select owner_id from user where email = ''

The code now reads

function getUserid( $email ) {  

     $sql = "select owner_id from user where email = '$email'";  

     $dbobj = mysql_query( $sql ) or  die ("Problem with SQL: $sql");  

     $dbarray = mysql_fetch_array( $dbobj );  

     return $dbarray[0];

Perhaps put the SELECT part in all caps so it differentiates with the rest? I will try that. can't hurt right?

Edit:changing it to all caps didn't help. Didn't hurt either though.

question - $sql is used further 'up' in the code I beleive. Could this be causing a conflict?

eoghain

Well, does the variable $email have any data in it before you pass it to your getUserid function?

Anvicuth

It should be. I'm uncertain if they can be used in multiple commands but I know the function just above this one calls $email successfully.

eoghain

I see the problem its in how you are calling getUserid. You will have to do something with the variable that is returned, like echoing it out.

<p>Thank you <b><? echo $uname; ?></b>, your information has been added to the database, you may now <a href="main.php" title="Login">log in</a> using your User Number, which is <? echo getUser_id( $email ); ?>. Please write this down as you must have it to log in.</p>

Anvicuth

phew. this is like trying to find a needle in the haystack...The error has moved and changed.

51>function displayStatus(){
52>   $uname = $_SESSION['reguname'];
53>   if($_SESSION['regresult']){
54>?>
55>
56> <h1>Registered!</h1>


[QUOTE]Parse error: parse error, expecting `','' or `';'' in /home/greydawn/www/register.php on line 55[/QUOTE] 


I put the numbers in so you see what is on line 55. The #> isn't actually in the code.

Roger_Ramjet

I see what you are about now, this is a 'sign up' script. Look use the attached scripts to do it all for you. It is from Kevin Yank and works 'right out of the box'. It will be quicker to customise for your needs than trying to debug what we have here. See the article that explains it here: http://www.sitepoint.com/print/users-php-sessions-mysql.

It uses the input email address to send the login details to the user, reroute them to the screen if you prefer. Instead of using a random no, it does an MD5 hash on the system time to generate a random char string as a password, which should work just as well.

Anvicuth

Thanks. I'll take a look at it.

Then I'll be back.

Anvicuth

Hmm. For the script you suggested, would it be possible to simply switch Username and Email? I want it so that username is not important to the database (So ou can have two Johns if you want). the important unique one is the ID number (Owner_id in the above script) that they use to login. The code would see if the email has aleady been used to register though.

So perhaps switch out username for email, then print the matching Owner_id?