Zymic Forums

Webmaster resources

Zymic IRC Server

Chat in real time at irc.zymic.com - Learn More

Welcome

Welcome to the Zymic webmaster forums. Our forums are here to provide people the free ability to discuss a range of websites related topics such as design, development coding and marketing.

In order to post you will need to register for a zymic account or if you already have one simply login by using the form on the left.

left Zymic Webmaster ForumsZymic Free Web HostingDatabases & MySQL right
  Closed Topic Start new topic
left
Mysql Insert/delete/query Tutorial
 right
Mortalis9
post Nov 15 2008, 07:20 AM
Post #1


Super Ninja
****

Group: Members
Posts: 357
Joined: 4-November 07
From: Los Angeles
Member No.: 2,317
Hey guys, this is my first tutorial for Zymic.com, so if you notice any spelling/code errors feel free to point them out smile.gif
First we're going to make a database, and then create a table with 4 rows identical to whats shown in the image below

Auto-increment means everytime a record is added, it will automatically increase by 1. This can be useful if you are wanting to assign unique member ID's to different people, or if you're counting the amount of messages submitted to a shoutbox.

Next we're going to learn how to insert items into a DB using a form. After we cover that, we will learn how to:
  1. Delete records by typing in an ID number via a form.
  2. Query for any item using ID #, subject, body, or author.
  3. List the mysql content on homepage


Open up your conventional PHP/HTML/CSS editor, and copy/paste this backbone insert code

Name this file insert.php

CODE
<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("dbname", $con);
$sql="INSERT INTO table (id, author, subject, body)
VALUES
('$_POST[id]','$_POST[author]','$_POST[subject]','$_POST[body]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Article added";

mysql_close($con)
?>


What this does, is basically take the variables ID , author, subject, and body, and posts them into the table located in the MySQL database.

After you've filled in the appropriate information in INSERT.PHP , make a file called INDEX.PHP

CODE
<html>
<body>

<!-- Form-to-MYSQL insertion code-->

<form action="insert.php" method="post">
Author: <input type="text" name="author">
Subject: <input type="text" name="subject" />
Body: <input type="text" name="body" width=500 height=500/>
<input type="submit" />
</form>

<!-- MYSQL Delete  Type in ID of post you want deleted-->

<form action="delete-db.php" method="post">
Delete Name: <input type="text" name="id" />
<input type="submit" />
</form>

<!-- MYSQL Query  Type in ID of post you want to search for-->

<form action="query.php" method="post">
Search for ID: <input type="text" name="query" />
<input type="submit" />
</form>

<!-- Shows current DB content -->

Current List :

<?php
$con = mysql_connect("localhost","user","pass");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("dbname", $con);

$result = mysql_query("SELECT * FROM table");

echo "<table border='1'>
<tr>
<th>ID</th>
<th>Subject</th>
<th>Body</th>
<th>Author</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['subject'] . "</td>";
   echo "<td>" . $row['body'] . "</td>";
    echo "<td>" . $row['author'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>


</body>
</html>


Here's what it will look like (pretty bland , but we're just working on the code for now! cool.gif )



After you have the two files, INDEX.PHP and INSERT.PHP made, go ahead and make DELETE-DB.PHP

Insert this code in there

CODE
<?php
$con = mysql_connect("localhost","user","pw");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("dbname", $con);

mysql_query("DELETE FROM table WHERE id='$_POST[id]'");

mysql_close($con);
?>


Basically what this does is it retrieves the query from the form ($_POST[id]) , and deletes it from the DB. If a member registered through your script and you wanted to delete him from the site, just find his #ID and submit it in this form biggrin.gif


Next is the Query . . .

Open up editor and name the file QUERY.PHP

CODE
<?php
$con = mysql_connect("localhost","user","pass");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("dbname", $con);

$result = mysql_query("SELECT * FROM table
WHERE id='$_POST[query]'");

while($row = mysql_fetch_array($result))
  {
  echo $row['id'] . " " . $row['subject'] . " " . $row['body'] . " " . $row['author'];
  echo "<br />";
  }

?>


This queries the DB for the variable inserted in the form on INDEX.PHP , and then echoes the results in a form.


That's it for now, if you have any questions, feel free to reply / PM me .

wink.gif
Go to the top of the page 
 
  + Quote Post
Andrew
post Nov 16 2008, 04:56 AM
Post #2


Hosting Abuse Staff
*******

Group: Moderators
Posts: 2,933
Joined: 14-February 08
From: Fort Myers FL, USA
Member No.: 10,089
I deleted our banter, I won't be the final decision. If you still want the whole thing gone, reply and it's done.
Go to the top of the page 
 
  + Quote Post
« Next Oldest · Databases & MySQL · Next Newest »
 
 Closed Topic Start new topic
left
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
 right
0 Members:
left
Forum Jump
 right
 
Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

Lo-Fi Version Time is now: 18th May 2013 - 01:32 PM
Powered By IP.Board 2.2.2 © 2013  IPS, Inc.