Creating a table... the "hard way"
Yes, a long time ago, I decided to make a site that I'd keep just locally to store the contact details of all my friends to help me learn PHP/MySQL.
I have finally gotten down to making the site and can't seem to be able to create a table.
Here is the relevant code:
<?php
mysql_connect($host, $user, $pass);
mysql_select_db($database) or die(\"Unable to select database\" . mysql_error());
$query = \"CREATE TABLE 'contacts'(\"
. \"'cid' INT NOT NULL AUTO_INCREMENT PRIMARY KEY,\"
. \"'csurname' VARCHAR(125) NOT NULL,\"
. \"'clastname' VARCHAR(125) NOT NULL,\"
. \"'cdob' VARCHAR(10),\"
. \"'caddress' VARCHAR(125),\"
. \"'csuburb' VARCHAR(125),\"
. \"'ccity' VARCHAR(125),\"
. \"'ccountry' VARCHAR(125),\"
. \"'cphone' VARCHAR(25),\"
. \"'cmobile' VARCHAR(25),\"
. \"'cemail' VARCHAR(125));\";
mysql_query($query);
if(isset($result)) {
mysql_free_result($result);
}
mysql_close();
?>I have worked with MySQL a long time ago in programming class but never with PHP. It's something simple that I am missing isn't it?
CptAwesome posted this at 05:51 — 18th March 2005.
He has: 370 posts
Joined: Dec 2004
$query = "CREATE TABLE 'contacts'("
should be
$query = "CREATE TABLE `contacts`("
or
$query = "CREATE TABLE contacts("
columns and tables are referred to with ` (left of the 1 key) and values use the quote '
Change all your 's to `s and it should work.
Greg K posted this at 05:56 — 18th March 2005.
He has: 2,145 posts
Joined: Nov 2003
I don't think you are having problem with the php, but the SQL instead (hence I moved which area your message is in).
When I took your SQL and tried to issue that via phpMyAdmin, the sql itself errored out. I believe it is becasue you are using a single quote (') around your table name/fields instead of the backquote, I think it is called (`), next to the 1 key.
After changing the sql to use those instead, it executed just fine.
-Greg
CptAwesome posted this at 07:18 — 18th March 2005.
He has: 370 posts
Joined: Dec 2004
Great minds think alike
Renegade posted this at 09:36 — 18th March 2005.
He has: 3,022 posts
Joined: Oct 2002
Ahh... I see... it works now...
Thanks.
Look forward to seeing more questions on PHP/MySQL by me
Renegade posted this at 11:51 — 18th March 2005.
He has: 3,022 posts
Joined: Oct 2002
Right, next problem...
I have this query:
<?php$query = \"INSERT INTO `\" . $table . \"` VALUES('', \"
.\"'\" . trim($_POST['add_surname']) . \"', \"
.\"'\" . trim($_POST['add_firstname']) . \"', \"
.\"'\" . trim($_POST['add_dob']) . \"', \"
.\"'\" . trim($_POST['add_sex']) . \"', \"
.\"'\" . trim($_POST['add_address']) . \"', \"
.\"'\" . trim($_POST['add_suburb']) . \"', \"
.\"'\" . trim($_POST['add_city']) . \"', \"
.\"'\" . trim($_POST['add_country']) . \"', \"
.\"'\" . trim($_POST['add_phone']) . \"', \"
.\"'\" . trim($_POST['add_mobile']) . \"', \"
.\"'\" . trim($_POST['add_email']) . \"', \"
.\"'\" . trim($_POST['add_website']) . \"');\";
?>
returning this error:
Column count doesn't match value count at row 1I googled for this and the sites all say that it has to do with my ID, but I am still not too sure how to fix it. Apprently, it is a very common error...
timjpriebe posted this at 13:10 — 18th March 2005.
He has: 2,667 posts
Joined: Dec 2004
One workaround would be to write it more like this:
insert into TABLENAME ( field1, field2) values ('textvalue', numvalue)
You would just leave out any auto-incrementing index.
Tim
http://www.tandswebdesign.com
dk01 posted this at 14:19 — 18th March 2005.
He has: 516 posts
Joined: Mar 2002
Yes I would always leave in the column names. That way you can check exactly what values are going where. Also in your original php you have:
mysql_query($query);'but it should be:
$result = mysql_query($query);'Otherwise there is no result object to clear
-dk
Greg K posted this at 21:16 — 18th March 2005.
He has: 2,145 posts
Joined: Nov 2003
The more important reason to inlcude all column names is that way if you ever change the table structure later (like add or delete columns), you are not dependant on the order.
To clarify, the error you are receiveing is becasue the # of items you are inserting into the table does not match the number of columns in the table. (I've done this before).
As you are indicating you will liekley have more SQL questions, I would offer this tip. When possible if you have a problem test the actual SQL command by hand either by logging into mySQL thought a shell account, or use something like phpMyAdmin where you can directly issue the SQL command. Sometimes this will geive you a better error message and help you narrow down the problems. To aid in doing this, on the line of code where you issue the mysql_query, right before it echo out the actual SQL statement. (this has helped me many times figure out i missed a quote or a coma).
Also in reference to phpMyAdmin, until you are used to it, this can help you properly format the php code you need, as there is a link after you execute commands for "show PHP code" (If i remember right, you do need to adjust the code that is given to have some spaces at the ends of the line, may be fixed now).
Yes I like phpMyAdmin, that and Zend Studio are two of my fav tools for php programming.
-Greg
PS. to Cpt: I had the reply all written, just got delayed in hitting send becasue my lady come to visit. Idea for the system: if you from when you hit REPLY till when you hit SEND/POST, if it detects there has been a new reply, let you know before accepting post.
Renegade posted this at 03:34 — 19th March 2005.
He has: 3,022 posts
Joined: Oct 2002
I get this error:
Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in c:\phpdev\www\public\contacts\content\add.php on line 23<?php
mysql_connect($host, $user, $pass) or die(\"Unable to connect to database: \" . mysql_error());
mysql_select_db($database) or die(\"Unable to select database: \" . mysql_error());
if($_POST['action'] == \"add\") {
$query = \"INSERT INTO \" . $table . \"(cid, csurname, cfirstname, cdob, csex, caddress, csuburb, ccity, ccountry, cphone, cmobile, cemail, cwebsite) VALUES('', \"
.\"'\" . trim($_POST['add_surname']) . \"', \"
.\"'\" . trim($_POST['add_firstname']) . \"', \"
.\"'\" . trim($_POST['add_dob']) . \"', \"
.\"'\" . trim($_POST['add_sex']) . \"', \"
.\"'\" . trim($_POST['add_address']) . \"', \"
.\"'\" . trim($_POST['add_suburb']) . \"', \"
.\"'\" . trim($_POST['add_city']) . \"', \"
.\"'\" . trim($_POST['add_country']) . \"', \"
.\"'\" . trim($_POST['add_phone']) . \"', \"
.\"'\" . trim($_POST['add_mobile']) . \"', \"
.\"'\" . trim($_POST['add_email']) . \"', \"
.\"'\" . trim($_POST['add_website']) . \"');\";
echo $query . \"<br />\";
$result = mysql_query($query);
if(!$result) {
echo \"No result: \" . mysql_error();
}
else {
echo $result;
mysql_free_result($result);
}
}
mysql_close();
?>
Strange thing is, when I copy and paste the output of $query into phpmyadmin, it will add the row just fine...
...I don't get all this database stuff :S
dk01 posted this at 04:54 — 19th March 2005.
He has: 516 posts
Joined: Mar 2002
I would try this instead. Results return an object memory address no matter if any results were found. Instead use mysql_num_rows($result) like so:
<?php
mysql_connect($host, $user, $pass) or die(\"Unable to connect to database: \" . mysql_error());
mysql_select_db($database) or die(\"Unable to select database: \" . mysql_error());
if($_POST['action'] == \"add\") {
$query = \"INSERT INTO \" . $table . \"(cid, csurname, cfirstname, cdob, csex, caddress, csuburb, ccity, ccountry, cphone, cmobile, cemail, cwebsite) VALUES('', \"
.\"'\" . trim($_POST['add_surname']) . \"', \"
.\"'\" . trim($_POST['add_firstname']) . \"', \"
.\"'\" . trim($_POST['add_dob']) . \"', \"
.\"'\" . trim($_POST['add_sex']) . \"', \"
.\"'\" . trim($_POST['add_address']) . \"', \"
.\"'\" . trim($_POST['add_suburb']) . \"', \"
.\"'\" . trim($_POST['add_city']) . \"', \"
.\"'\" . trim($_POST['add_country']) . \"', \"
.\"'\" . trim($_POST['add_phone']) . \"', \"
.\"'\" . trim($_POST['add_mobile']) . \"', \"
.\"'\" . trim($_POST['add_email']) . \"', \"
.\"'\" . trim($_POST['add_website']) . \"');\";
echo $query . \"<br />\";
$result = @mysql_query($query);
if(mysql_num_rows($result)>0) {
echo \"No result: \" . mysql_error();
}
else {
while ( $row = mysql_fetch_array($result) ){
echo($row['FIELD TO DISPLAY HERE']);
}
mysql_free_result($result);
}
mysql_close();
?>
edit :// You were also trying to print a result. Instead you have to print a field. Try the code above but try entering one of the fields you want to display instead of FIELD TO DISPLAY HERE.
Hopefully that solves it.
-dk
Renegade posted this at 00:58 — 21st March 2005.
He has: 3,022 posts
Joined: Oct 2002
Problem solved. Turned out that I was trying to free a result which didn't exist. Should have read the manual more carefully - http://nz.php.net/manual/en/function.mysql-query.php
Since I was using an INSERT query, it was returning a "1" instead of an actual resource ID so there was nothing to free.
Renegade posted this at 10:56 — 21st March 2005.
He has: 3,022 posts
Joined: Oct 2002
OK, I'm looping through and outputting the result using this function:
<?phpfunction fnBrowse($result) {
while($line = mysql_fetch_array($result)) {
echo \"<tr>\";
for($i = 0; $i <= ((count($line) / 2) - 1); $i++) {
echo \"<!-- line $i -->\";
echo \"<td>\" . $line[$i] . \"</td>\";
}
echo \"</tr>\n\";
}
}
?>
Problem is, how do I check if the current line is "cemail" or not? I want that so that I can make it into a link. The same for "cwebsite".
CptAwesome posted this at 17:20 — 21st March 2005.
He has: 370 posts
Joined: Dec 2004
To be honest, I don't know why you'd bother with a for loop, an extra few lines of code would save you this headache. But I am pretty sure, the following would do the trick:
function fnBrowse($result) {while($line = mysql_fetch_array($result)) {
echo "<tr>";
for($i = 0; $i <= ((count($line) / 2) - 1); $i++) {
echo "<!-- line $i -->";
if($line[$i] == $line[cemail]){
echo "<td><a href=\"mailto:" . $line[$i] . "\">". $line[$i] . "</a></td>";
}
if($line[$i] == $line[cwebsite]){
echo "<td><a href=\"" . $line[$i] . "\">". $line[$i] . "</a></td>";
}
echo "<td>" . $line[$i] . "</td>";
}
echo "</tr>\n";
}
}
Renegade posted this at 19:13 — 21st March 2005.
He has: 3,022 posts
Joined: Oct 2002
Yes, the code did work. Thanks.
The reason I do it this way is because I want to show the table of contacts on several pages and it would save a lot of time and recoding if I just put it into a function and made it more "generic"
CptAwesome posted this at 19:33 — 21st March 2005.
He has: 370 posts
Joined: Dec 2004
putting it in a function is ok, but it's the for loop that confuses me, I mean, you could just have it echo one TD at a time, and you'd only have an extra couple lines.
Renegade posted this at 03:51 — 22nd March 2005.
He has: 3,022 posts
Joined: Oct 2002
Well, yes, I could do it that way, but the thing is, this way, it is more dynamic. As in, I can just query for a "cid", "csurname", and "cfirstname" and show those then, call the function again to show "cid", "csurname", "cfirstname" and "cemail"
...Get it?
CptAwesome posted this at 07:02 — 22nd March 2005.
He has: 370 posts
Joined: Dec 2004
Yeah, I suppose that makes sense.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.