beginner question - first real "script"

[vampke]

Hi guys,

 

This is probably so basic that most of you will roll their eyeballs while reading this, but I can't figure this out 

I want to write a script where users enter some numbers on a website. The script gets all the data from the mysql table and starts to compare these data with the numbers entered. In the end the result that is closest is printed.

 

I was thinking that the "compare function" should be something like this: user_number - mysql_data = result. if result = 0 than it compares well, the higher it gets, the less 'points' for this mysql_data is given. So far so good (I hope!?)

 

Now, I need to do this calculation on every row in the table, and this is where I'm stuck. How do I go about this?

Do I need to use a For statement that increments after every run of the cycle?

 

What about the results I get after a run of the cycle? Do I put them in an array so I can work with them later (to see which results is most like the user's input)?

 

Thanks for helping me out!

[lewis987]

ok, you will be using html and PHP at the same time, so first off make a form with a button and a text field (basic html), set the method to post so it should look like:

 

<form action="" method="post">
<input name="searchQuery" type="text" /><br />
<input name="search" type="submit" value="Search Members" />
</form>

 

so you have a text field and a submit button.

 

now to do the hard part:

 

<?PHP

Start the PHP

 

if($_POST['search']){

So it checks if the button has been pressed

 

mysql_connect("localhost","username","password") or die(mysql_error());
mysql_select_db("database_name") or die("Cannot select the database");

Connects to the database, Make sure you put your own details in it.

 

$search = $_POST['searchQuery'];

Sets a variable called "search" with the data from the form.

 

$checkSQL = "SELECT * FROM `numbers` WHERE `number` = '$search' ;";

sets another variable with the SQL data for searching the database. CHANGE `numbers` and `number` to your own.

 

$checkQuery = mysql_query($checkSQL);

Sets another variable to query the database.

 

if(mysql_num_rows($checkQuery) > 0){

Sets up an If statement to check the database records to make sure that the data is more than 0.

 

while($number = mysql_fetch_array($checkQuery)){

Set up a while loop so it continues to show the data until all the data is shown

 

echo $number['number'];

Shows the data from the database. CHANGE 'number' to your own, but remember the apostraphies ( ' )

 

}

Close the loop

 

}

close the if statement

 

else
{

If the rows return nothing then show the error message.

 

echo "Cannot find numbers";

Show the error message.

 

}

close the else part

 

}

close the first if statement.

 

?>

Close PHP

 

Now your page should look like this:

 

<?PHP
if($_POST['search']){

mysql_connect("localhost","username","password") or die(mysql_error());
mysql_select_db("database_name") or die("Cannot select the database");

$search = $_POST['searchQuery'];

$checkSQL = "SELECT * FROM `numbers` WHERE `number` = '$search' ;";

$checkQuery = mysql_query($checkSQL);

if(mysql_num_rows($checkQuery) > 0){
while($number = mysql_fetch_array($checkQuery)){

echo $number['number'];

}
}
else
{
echo "Cannot Find numbers";
}
}
?>
<form action="" method="post">
<input name="searchQuery" type="text" /><br />
<input name="search" type="submit" value="Search Members" />
</form>

 

It will not be valid under W3C, but remember, change everything to your own things

[vampke]

thanks a lot for your kind effort!

This is not exactly what I'm looking for though.

I actually want to have my users input several numbers. I'll be using radio boxes I think, ranging from 0 to 5.

With these results, the script needs to calculate the entry in the database that is closest to the user's input.

 

I thought I'd calculate the difference between the input and what is in the database and assign a value to that result (5 for result=0 : perfect match; 0 for result=5 : completely the opposite).

 

EG:

I like my women to have cup size: A B C D D+

I like women to weigh: below 50kg 50-60kg 60-70kg more_then_70kg

 

The script will compare cup size and weight the user entered with all the women in the database and come up with the closest match. If there is no perfect match, the script needs to give the closest (eg. there is no woman with D+ cup in the database, but there are two with the same weight that the user entered and with a D-cup, so the script will propose these)

 

My problem at the moment is that I can do this math with 1 result from the database, but I don't know how to run this script through all the database rows and compare these results with one another

[lewis987]

ahh, ok i cant give you the code as i dont have one made up 

[GingerRobot]

If ive understood the problem properly, you can achieve this just with your mysql query. I tested this:

 

<?php
include('test_db_connection.php');
$cupsize = 3;
$weight = 2;
$sql = "SELECT `cupsize`,`weight` FROM `phpfreaks` ORDER BY (ABS(cupsize-'$cupsize')+ABS(weight-'$weight'))";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($result)){
echo 'Cupsize:'.$row['cupsize'].' Weight:'.$row['weight'].'<br />';
}
?>

 

And it seems to work.

 

So, you have a table in your database with fields for cupsize and weight, which contain a number from 1-5. In the query you select cupsize and weight, but its the ordering that gets you what you want. You take the absolute value of the cupsize field minus the value you are after( you use absolute value so all values are positive) and then the absolute value of the weight field mins the weight you want. Add those together. Then order by that. With the 4 pieces of test data i used, i got:

 

Cupsize:3 Weight:2
Cupsize:3 Weight:3
Cupsize:3 Weight:4
Cupsize:1 Weight:5

[vampke]

@GingerRobot,

 

Thank you ever so much!

This is excellent! I did not know SQL statements could be so powerfull.

Great stuff! Thanks!

[GingerRobot]

No problem

 

Took me a long time to even begin to appreciate what you can do with a query.

[vampke]

If you guys don't mind, I would like to come back to this.

The script seems to be working like a charm, but now I want to sort the results not with a query but in the php script.

 

how would i do this?

[thefortrees]

Depends on how you want to sort... You could place all values in an array then use the various PHP array sort functions. Read here to get you started - http://us.php.net/manual/en/function.sort.php