eMonk

Guess I'll go with $query = "SELECT column1, column2, column3, column4, .... FROM model"; because it makes more sense and just fetches the data I'll be using. The first query is just smaller code so I was wondering which would be better but after some thought I think the second one would be better unless someone else says otherwise.

One of my tables has 28 columns. I'm going to be echoing 9 of the columns in my index file. Which of the following queries would be better to do this as they both work: $query = "SELECT * FROM model"; $query = "SELECT column1, column2, column3, column4, .... FROM model";

Why do I always get the wrong answers on this forum lol. Just saying.

My book recommends using char over varchar because it will keep the database smaller and it's faster to phrase. So why is everyone using varchar?

I'm trying to update the genre in a php/mysql form though. Is it possible to select the genre stored in the database, for example, Female and then have the other option under it (Male) so I can update it to Male if needed and vice versa? I was thinking along the lines of: <option><? echo($genre); ?></option> But not sure how you would include the other options or if this can even be done.

I'm not sure what to search for or if this can be done... Let's say I have a mysql table named "genre". Now I have "Male" and "Female" in a dropdown menu like this in a form: <select name="genre"> <option>Male</option> <option>Female</option> </select> How would you display, for example, the option Female in a update.php file if that's the genre stored in the mysql database when you fetch the results?

Here's list.php that works. The code listed above is update.php. <?php $host="xxxxx"; // Host name $username="xxxxx"; // Mysql username $password="xxxxx"; // Mysql password $db_name="xxxxx"; // Database name $tbl_name="model"; // Table name // Connect to server and select database. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); $sql="SELECT model_id, model_name, location, email_1 FROM $tbl_name"; $result=mysql_query($sql); ?> <table width="400" border="0" cellspacing="1" cellpadding="0"> <tr> <td> <table width="400" border="1" cellspacing="0" cellpadding="3"> <tr> <td colspan="4"><strong>List data from mysql </strong> </td> </tr> <tr> <td align="center"><strong>Name</strong></td> <td align="center"><strong>Location</strong></td> <td align="center"><strong>Email</strong></td> <td align="center"><strong>Update</strong></td> </tr> <?php while($rows=mysql_fetch_array($result)){ ?> <tr> <td><?php echo $rows['model_name']; ?></td> <td><?php echo $rows['location']; ?></td> <td><?php echo $rows['email_1']; ?></td> // link to update.php and send value of id <td align="center"><a href="update.php?id=<? echo $rows['model_id']; ?>">update</a></td> </tr> <?php } ?> </table> </td> </tr> </table> <?php mysql_close(); ?>

No errors returned. I'm stumped man. Been searching this site and google for clues for the past few hours. I can't believe how difficult this is just to echo mysql results in a table.

Done but the problem still remains (echo values aren't being displayed).

<?php $host="xxxx"; // Host name $username="xxxx"; // Mysql username $password="xxxx"; // Mysql password $db_name="xxxx"; // Database name $tbl_name="xxxx"; // Table name // Connect to server and select database. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); // get value of id that sent from address bar $model_id=$_GET['model_id']; // Retrieve data from database $sql="SELECT model_id, model_name, location, email_1 FROM $tbl_name WHERE model_id='$model_id'"; $result=mysql_query($sql); $rows=mysql_fetch_array($result); ?> <table width="400" border="0" cellspacing="1" cellpadding="0"> <tr> <form name="form1" method="post" action="updated.php"> <td> <table width="100%" border="0" cellspacing="1" cellpadding="0"> <tr> <td> </td> <td colspan="3"><strong>Update data in mysql</strong> </td> </tr> <tr> <td align="center"> </td> <td align="center"> </td> <td align="center"> </td> <td align="center"> </td> </tr> <tr> <td align="center"> </td> <td align="center"><strong>Name</strong></td> <td align="center"><strong>Location</strong></td> <td align="center"><strong>Email</strong></td> </tr> <tr> <td> </td> <td align="center"><input name="name" type="text" id="name" value="<? echo $rows['model_name']; ?>"></td> <td align="center"><input name="location" type="text" id="location" value="<? echo $rows['location']; ?>"></td> <td><input name="email" type="text" id="email" value="<? echo $rows['email_1']; ?>"></td> </tr> <tr> <td> </td> <td><input name="id" type="hidden" id="id" value="<? echo $rows['model_id']; ?>"></td> <td align="center"><input type="submit" name="Submit" value="Submit"></td> <td> </td> </tr> </table> </td> </form> </tr> </table> <? // close connection mysql_close(); ?> This is the part that isn't working <td align="center"><input name="name" type="text" id="name" value="<? echo $rows['model_name']; ?>"></td> <td align="center"><input name="location" type="text" id="location" value="<? echo $rows['location']; ?>"></td> <td><input name="email" type="text" id="email" value="<? echo $rows['email_1']; ?>"></td> The echo values aren't being fetched and left blank when I run the script. Any ideas?

Would it be safer to just use my old short variable names instead of extract($_POST)? I'm not too sure what the php manual meant on extract().

Oh wow, that is awesome.. thanks bro!

By the way, what did you mean by extract($_POST) Do I just use that instead of my short variable names?

I have it working now...forgot to set NULL as the first value as I'm using model_id int unsigned not null auto_increment primary key No idea why it was giving me those login/password errors as it was correct.

@ $db = new mysqli('host', 'username', 'password', 'database'); // these values were removed I just logged into the mysql server via putty with this info and it worked.

Warning: mysql_query() [function.mysql-query]: Access denied for user 'root'@'localhost' (using password: NO) in /usr/www/virtual/user/domain/v1/admin/insert-model.php on line 75 Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /usr/www/virtual/user/domain/v1/admin/insert-model.php on line 75 Access denied for user 'root'@'localhost' (using password: NO)

Warning: Wrong parameter count for mysql_result() in /usr/www/virtual/user/domain/v1/admin/insert-model.php on line 75 line 75 is: $result = mysql_result ( $query ) or die ( mysql_error() ) ;

I added in the following code but get the same error: $query = "insert into model (model_name, age, height, hair, measurements, weight, eyes, service, nationality, location, city_1, city_2, city_3, city_4, phone, email_1, email_2, website, description, schedule, thumbnail, url, status, views, expiry_date, notes) values ('$name', '$age', '$height', '$hair', '$measurements', '$weight', '$eyes', '$service', '$nationality', '$location', '$city_1', '$city_2', '$city_3', '$city_4', '$phone', '$email_1', '$email_2', '$website', '$description', '$availability', '$thumbnail', '$url', '$status', '$views', '$expiry_date', '$notes')"; $result = $db->query($query);

The php/mysql code below is called after a user fills out a html form and click on submit: insert-model.php <html> <head> <title>Untitled Document</title> </head> <body> <h1>Model Entry Results</h1> <?php // create short variable names $name=$_POST['name']; $age=$_POST['age']; $height=$_POST['height']; $hair=$_POST['hair']; $measurements=$_POST['measurements']; $weight=$_POST['weight']; $eyes=$_POST['eyes']; $service=$_POST['service']; $nationality=$_POST['nationality']; $location=$_POST['location']; $city_1=$_POST['city_1']; $city_2=$_POST['city_2']; $city_3=$_POST['city_3']; $city_4=$_POST['city_4']; $phone=$_POST['phone']; $email_1=$_POST['email_1']; $email_2=$_POST['email_2']; $website=$_POST['website']; $description=$_POST['description']; $availability=$_POST['availability']; $thumbnail=$_POST['thumbnail']; $url=$_POST['url']; $status=$_POST['status']; $views=$_POST['views']; $expiry_date=$_POST['expiry_date']; $notes=$_POST['notes']; if (!$name || !$thumbnail || !$url || !$views || !$expiry_date) { echo "You have not entered all the required details.<br />" ."Please go back and try again."; exit; } if (!get_magic_quotes_gpc()) { $name = addslashes($name); $height = addslashes($height); $hair = addslashes($hair); $measurements = addslashes($measurements); $eyes = addslashes($eyes); $nationality = addslashes($nationality); $location = addslashes($location); $phone = addslashes($phone); $email_1 = addslashes($email_1); $email_2 = addslashes($email_2); $website = addslashes($website); $description = addslashes($description); $availability = addslashes($availability); $thumbnail = addslashes($thumbnail); $url = addslashes($url); $expiry_date = addslashes($expiry_date); $notes = addslashes($notes); } @ $db = new mysqli('host', 'username', 'password', 'database'); // these values were removed if (mysqli_connect_error()) { echo "Error: Could not connect to database. Please try again later."; exit; } $query = "insert into model values ('".$name."', '".$age."', '".$height."', '".$hair."', '".$measurements."', '".$weight."', '".$eyes."', '".$service."', '".$nationality."', '".$location."', '".$city_1."', '".$city_2."', '".$city_3."', '".$city_4."', '".$phone."', '".$email_1."', '".$email_2."', '".$website."', '".$description."', '".$availability."', '".$thumbnail."', '".$url."', '".$status."', '".$views."', '".$expiry_date."', '".$notes."')"; $result = $db->query($query); if ($result) { echo $db->affected_rows." service provider inserted into the database."; } else { echo "An error has occurred. The model was not added."; } $db->close(); ?> </body> </html> I keep getting the following error: "An error has occurred. The model was not added." Any ideas?

Ah that makes sense. Thanks ignace. How would you do multiple city values (up to 4) in the model_in_city table?

Hello everyone! I'm working on my first php/mysql project and need some guidance how I should structure this database. It will be for a modeling site. - on the main page a list of cities and provinces for the users to choose from. - once a city is chosen, a list of models from that area will be displayed in thumbnails. - once a model is picked from the city list, her bio page will appear with the following info: - age - height - hair - measurements - eyes - description The main page will also include a search feature and pagination so they can display 15-30-45-etc thumbs at a time. The models can also choose up to 4 cities to be placed it so I would need to have multiple values in the city entry. Any idea how I can structure this? I was going to put these in 1 table but read that's a bad idea and can't think how I would do this using multiple tables and need some advise. Thanks in advance!

update: i had some typos in the book_insert.sql file during my 1st attempt. in result duplicate data was detected when i performed the same query again. i used TRUNCATE TABLE tablename; then performed the query & now it works.

thanks POG1 but where do i add that? sorry mysql newbie here.

I get this error when trying to run this mysql command: mysql -h xxx -u xxx -p books < /path/to/book_insert.sql book_insert.sql use books; insert into customers values (3, 'Julie Smith', '25 Oak Street', 'Airport West'), (4, 'Alan Wong', '1/47 Haines Avenue', 'Box Hill'), (5, 'Michelle Arthur', '357 North Road', 'Yarraville'); insert into orders values (NULL, 3, 69.98, '2007-04-02'), (NULL, 1, 49.99, '2007-04-15'), (NULL, 2, 74.98, '2007-04-19'), (NULL, 3, 24.99, '2007-05-01'); insert into books values ('0-672-31697-8', 'Michael Morgan', 'Java 2 for Professional Developers', 34.99), ('0-672-31745-1', 'Thomas Down', 'Installing Debian GNU/Linux', 24.99), ('0-672-31509-2', 'Pruitt, et al.', 'Teach Yourself GIMP in 24 Hours', 24.99), ('0-672-31769-9', 'Thomas Schenk', 'Caldera OpenLinux System Administration Unleashed', 49.99); insert into order_items values (1, '0-672-31697-8', 2), (2, '0-672-31769-9', 1), (3, '0-672-31769-9', 1), (4, '0-672-31509-2', 1), (5, '0-672-31745-1', 3); insert into book_reviews values ('0-672-31697-8', 'The Morgan book is clearly written and goes well beyond most of the basic Java books out there.'); bookorama.sql create table customers ( customerid int unsigned not null auto_increment primary key, name char(50) not null, address char(100) not null, city char(30) not null ); create table orders ( orderid int unsigned not null auto_increment primary key, customerid int unsigned not null, amount float(6,2), date date not null ); create table books ( isbn char(13) not null primary key, author char(50), title char(100), price float(4,2) ); create table order_items ( orderid int unsigned not null, isbn char(13) not null, quantity tinyint unsigned, primary key (orderid, isbn) ); create table book_reviews ( isbn char(13) not null primary key, review text ); any ideas? i'm new to mysql and this is my first experiment.

nevermind, found a different working code... thx