...

View Full Version : Easy question with PHP output/sorting



Derek Zoolander
08-07-2007, 04:34 AM
I have the user enter first name, last name, make/model, and license plate. I want to output each user's info in on a separate row. Whenever I have more than 2 users, it mix/matches all the info into like every possibility lol. What am I doing wrong?


<?php
$link = mysql_connect("xxx", "xxx", "xxx") or die("connect
error:".mysql_error());
$db = "xxx";
mysql_select_db($db, $link) or die("select DB error:".mysql_error());
$result = mysql_query("SELECT * FROM fname, lname, car, licenseplate");
while($row = mysql_fetch_array($result))
{
echo $row['lname'] .', '. $row['fname'] .' - '. $row['make'] .' '. $row['model'] .' - '. $row['licenseplate'];
echo '<br>';
}
mysql_close($link);
?>


*make and model are attributes of car

Fumigator
08-07-2007, 05:28 AM
Do you really have four different tables called fname, lname, car, and licenseplate?

Derek Zoolander
08-07-2007, 05:32 AM
lol, just realized I pasted the wrong code. I changed to name with attributes fname and lname. Car with attributes make and model. Then License plate.
I'm a beginner just trying to learn.

here you go:


<?php
$link = mysql_connect("xxx", "xxx", "xxx") or die("connect
error:".mysql_error());
$db = "xxx";
mysql_select_db($db, $link) or die("select DB error:".mysql_error());
$result = mysql_query("SELECT * FROM name, car, licenseplate");
while($row = mysql_fetch_array($result))
{
echo $row['fname'] .', '. $row['lname'] .' - '. $row['make'] .' '. $row['model'] .' - '. $row['licenseplate'];
echo '<br>';
}

mysql_close($link);
?>

PappaJohn
08-07-2007, 05:40 AM
I suspect that your tables are not properly "normalized". It would probably be a good idea to post your table structure, and get some advice on that before proceeding. It will likely make your life easier now and down the road with this project.

Derek Zoolander
08-07-2007, 06:03 AM
I'm not worried about normalization for now. This is just for fun.

Tables:
name - attributes: lname, fname
car - attributes: make, model
licenseplate - attributes: licenseplate (primary key)

I want to print all of this data on 1 line for each user, and sort by lname or licenseplate.

Derek Zoolander
08-07-2007, 06:04 AM
I'm not worried about normalization for now. This is just for fun.

Tables:
name - attributes: lname, fname
car - attributes: make, model
licenseplate - attributes: licenseplate (primary key)

I want to print all of this data on 1 line for each user, and sort by lname or licenseplate.

PappaJohn
08-07-2007, 06:18 AM
Well for fun or not, the data still needs to be normalized in order to obtain the results you want.

Since a license plate can only be associated with one car, I see no need in keeping that in a separate table. Also, you will need some way of associating the car information with the individual information.

I'd suggest a table structure like this:

name table:


id - primary key, int, auto-increment
lname - varchar of appropriate size
fname - varchar of appropriate size


car table


id - primary key, int, auto-increment
name_id - int
make - varchar of appropriate size
model - varchar of appropriate size
licenseplate - varchar of appropriate size

Now, when a user inputs their information, they'll be assigned an id for the name table automatically. You can use this value as the name_id in the car table.

Now, you can associate a car with a user by the name_id field. You'll also be able to sort your results as you wish. You can create keys on the lname field in the name table and the licenseplate field in the car table.

Of course, if your only allowing users to have one car, you can keep it all in one table.

Derek Zoolander
08-07-2007, 06:34 AM
Alright, sometimes I have trouble thinking of it that way. I'll try it out. Users can only have 1 car, so could I do?:

Name table:
id
lname
fname
make
model
licenseplate

PappaJohn
08-07-2007, 07:50 AM
yep, that would work.

Derek Zoolander
08-09-2007, 02:42 AM
Ok, so how would i write the output? I don't get what the point of having an id is. Can't I just output $row['fname'] .' '. $row['lname'] etc.?

PappaJohn
08-09-2007, 04:44 AM
The id was needed when you were using multiple tables - it was used to associate records from 1 table to the related records in another table.

Since you've switched to 1 table you can ignore it in your output.

Derek Zoolander
08-09-2007, 10:18 PM
Right.
Ok, so this is what i've got. It's outputting the correct info that is already in the database, but now the form submit isn't working to populate the database. Here's the code:

<form method="POST" action=register.php>
First Name: <input type="text" name="fname"><br><br>
Last Name: <input type="text" name="lname"><br><br>
Make: <input type="text" name="make"><br><br>
Model: <input type="text" name="model"><br><br>
State: <input type="text" name="state"><br><br>
License Plate: <input type="text" name="licenseplate"><br><br>
<input type=submit value="Submit">
</form>



<?php
$link = mysql_connect("xxx", "xxx", "xxx") or die("connect
error:".mysql_error());
$db = "xxx";
mysql_select_db($db, $link) or die("select DB error:".mysql_error());
$fname = $_REQUEST['fname'];
$lname = $_REQUEST['lname'];
$make = $_REQUEST['make'];
$model = $_REQUEST['model'];
$licenseplate = $_REQUEST['licenseplate'];
$result = mysql_query("INSERT INTO name VALUES ('$fname', '$lname', '$make', '$model', '$licenseplate')");
print "<br><br>Your information has been entered.<br> Redirecting...";
mysql_close($link);
?>

PappaJohn
08-09-2007, 10:27 PM
If you don't specify field names in your INSERT, then you must supply values for all fields.

Also, always add error checking to your db operations, it can help point out problems for you.


$result = mysql_query("INSERT INTO name VALUES ('$fname', '$lname', '$make', '$model', '$licenseplate')") or die(mysql_error());

Derek Zoolander
08-09-2007, 10:40 PM
Do you mean it should be something like this? I'm kinda confused now, because I thought I was doing it right:


$result = mysql_query("INSERT '$fname', '$lname', '$make', '$model', '$state', '$licenseplate' INTO name VALUES ('$fname', '$lname', '$make', '$model', '$state', '$licenseplate')") or die(mysql_error());


Thanks for bearing with me.

Derek Zoolander
08-09-2007, 10:55 PM
Haha, I fixed it. I was missing a ' and something else. Thanks for all your help. I really appreciate it :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum