Results 1 to 10 of 10
  1. #1

    PHP Newbie needs help with querys pls

    I'm still a newb with php, and i've been messing with this for hours with no success.

    First, heres the sql dump for the three tables I'm using:

    CREATE TABLE news (
    id int(10) unsigned NOT NULL auto_increment,
    postdate timestamp(14) NOT NULL,
    title varchar(50) NOT NULL default '',
    newstext text NOT NULL,
    PRIMARY KEY (id),
    KEY postdate (postdate)
    ) TYPE=MyISAM;

    DROP TABLE IF EXISTS news_category;
    CREATE TABLE news_category (
    category_id bigint(20) NOT NULL auto_increment,
    PRIMARY KEY (category_id)
    ) TYPE=MyISAM;

    DROP TABLE IF EXISTS category;
    CREATE TABLE category (
    category_id bigint(20) NOT NULL auto_increment,
    category_name char(80) default NULL,
    PRIMARY KEY (category_id)
    ) TYPE=MyISAM;

    What i'm trying to do is give my news postings categories, so I can post news in categories and sort them out if a visitor wants to view only the specific ones posted in the category.

    the INSERT query im using to insert just the news looks like this:

    $result = mysql_query("INSERT INTO news (id, postdate, title, newstext)
    VALUES ('$id',NOW(),'$title','$newstext')",$connect);

    What sql query would I use if i was using form textboxes to send the datas?

    Also, what query would i use to display each news with its category and when users click the category link in a menu, what would the sql query be to display the category with news?

    I appreciate the help, ty

  2. #2
    Join Date
    Jul 2003
    You need to have a news_id field in your news_category table that links a category to a news story. Currently, there is no point to your news_category table, as there is no way to link the category to a news story.

    You only need a separate news_category table if one news story can belong to many categories (many-to-many relationship). If a news item can only belong to one category, then simply add a category_id field in your news table and link it with the primary key from your category table (and get rid of news_category).

    As far as your other question, you will need to do some error checking and cleanup before any information from the user is entered into a query.

    If your form method is "post" then all form fields will be in $_POST with the name attribute of the field being a key. If your form method is "get" then its $_GET.

    So if you have

    <form method="post" action="me.php">
    <input type="text" name="first_name" />
    <input type="submit" name="submit" value="Go" />

    To get the value of the first_name field box, you will need to echo $_POST['first_name']

    To make sure information is correctly escaped for MySQL, use mysql_real_escape_string() on any content that is to be used in a query.

    Hopefully this helps.
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

  3. #3
    thank you for the reply, I had thought about making a category row in the news table, since the news stories will belong to only 1 category. heh,

    I'll try what you said and add a category_id field to my news table. How exactly would i link it from the primary key in the category table? And to display each category by itsself with news when a visitor clicks the link would the sql query be something like this;

    $query = "SELECT * FROM news, category ORDER BY postdate DESC";

    or would it be:

    $query = "SELECT id,title,newstext," .
    "DATE_FORMAT(postdate, '%Y-%m-%d') as date, category_id " .
    "FROM news, category ORDER BY postdate DESC";

    Thanks for the help

  4. #4
    Ok, I got the code right to post the news. I just cant get it to show up with the category_id and category_name in the categorys table.

    This is the code used to display the news and comments:

    /* user config variables */
    $max_items = 5; /* max number of news items to show */

    /* make database connection */
    $db = mysql_connect ('localhost','user','pass');
    mysql_select_db ('database',$db);

    function displayNews($all = 0) {
    /* bring in two variables
    * $db is our database connection
    * $max_items is the maximum number
    * of news items we want to display */
    global $db, $max_items;

    /* query for news items */
    if ($all == 0) {
    /* this query is for up to $max_items */
    $query = "SELECT id,title,newstext," .
    "DATE_FORMAT(postdate, '%Y-%m-%d') as date " .
    "FROM news ORDER BY postdate DESC LIMIT $max_items";
    } else {
    /* this query will get all news */
    $query = "SELECT id,title,newstext," .
    "DATE_FORMAT(postdate, '%Y-%m-%d') as date " .
    "FROM news ORDER BY postdate DESC";
    $result = mysql_query ($query);
    while ($row = mysql_fetch_assoc ($result)) {
    /* display news in a simple table */

    /* place table row data in
    * easier to use variables.
    * Here we also make sure no
    * HTML tags, other than the
    * ones we want are displayed */
    $date = $row['date'];
    $title = htmlentities ($row['title']);
    $news = nl2br (strip_tags ($row['newstext'], '<a><b><i><u>'));

    /* display the data */
    echo"<div class='newsheadertext' align='left'>$title - $date</div>\n";
    echo "<BR><p align='left'>$news</p>\n";

    /* get number of comments */
    $comment_query = "SELECT count(*) FROM news_comments " .
    "WHERE news_id={$row['id']}";
    $comment_result = mysql_query ($comment_query);
    $comment_row = mysql_fetch_row($comment_result);

    /* display number of comments with link */
    echo "<div class='newsheadertext'><BR><p align='right'><b>(<a href=\"{$_SERVER['PHP_SELF']}" .
    "?action=show&id={$row['id']}\">" .

    /* finish up table*/
    echo "<BR>\n";

    function displayOneItem($id) {
    global $db;

    /* query for item */
    $query = "SELECT * FROM news WHERE id=$id";
    $result = mysql_query ($query);

    /* if we get no results back, error out */
    if (mysql_num_rows ($result) == 0) {
    echo "Bad news id\n";
    $row = mysql_fetch_assoc($result);

    /* easier to read variables and
    * striping out tags */
    $date = $row['date'];
    $title = htmlentities ($row['title']);
    $news = nl2br (strip_tags ($row['newstext'], '<a><b><i><u>'));

    /* display the items */
    echo"<div class='newsheadertext' align='left'>$title - $date></div>\n";
    echo "<BR><div class='newstext'>$news</div>\n";

    echo "<BR><hr color='#505050' width='100%'>\n";

    /* now show the comments */

    function displayComments($id) {
    /* bring db connection variable into scope */
    global $db;

    /* query for comments */
    $query = "SELECT * FROM news_comments WHERE news_id=$id";
    $result = mysql_query ($query);
    echo"<div class='newsheadertext' align='center'>Comments</div>\n";

    /* display the all the comments */
    while ($row = mysql_fetch_assoc ($result)) {
    echo "<br>\n";

    $name = htmlentities ($row['name']);
    echo "<p align='left'><b>$name</b> - \n";

    $site = htmlentities ($row['site']);
    echo "<b>(<a href='$site' target='_blank'>URL</a>)</b><BR><BR>\n";

    $comment = strip_tags ($row['comment'], '<a><b><i><u>');
    $comment = nl2br ($comment);
    echo "<b>Said</b>: $comment</p>\n";

    echo "<hr color='#505050' width='100%'>\n";

    /* add a form where users can enter new comments */
    echo "<center><BR>";
    echo "<FORM action=\"{$_SERVER['PHP_SELF']}" .
    "?action=addcomment&id=$id\" method=POST>\n";
    echo "<b>Name</b>:<BR> <input type=\"text\" " .
    "width=\"30\" name=\"name\"><BR><BR>\n";
    echo "<b>Your URL</b>:<BR> <input type=\"text\" " .
    "width=\"30\" name=\"site\"><BR>\n";
    echo "<TEXTAREA cols=\"40\" rows=\"3\" " .
    echo "<input type=\"submit\" name=\"submit\" " .
    "value=\"Add Comment\"\n";
    echo "</FORM></center><BR>\n";

    function addComment($id) {
    global $db;

    /* insert the comment */
    $query = "INSERT INTO news_comments " .

    echo "Comment entered. Thanks!<BR>\n";
    echo "<a href=\"{$_SERVER['PHP_SELF']}" .

    /* this is where the script decides what do do */

    switch($_GET['action']) {

    case 'show':
    case 'all':
    case 'addcomment':

    Sorry if the codes to long =( I tried inserting seperiate queries but kept getting parse errors, and I couldnt see where any semicolons or closing bracets were missing. meh

  5. #5
    Actually you'd have to use joins. so something like

    select title,newstext from news left join category using(category_id)

  6. #6
    hmmm, thanks for the tip, i'll try a join statement and check google for some other info on joins.

  7. #7
    I finally got it to work, ty all for the help and suggestions

    Posting in the same category requires putting the id and name in the boxes when posting but thats fine. I wont try using a drop down box if its gonna be a hassle.

  8. #8
    Join Date
    Jul 2003
    You don't need to put the name.

    You can build a drop down menu with all your categories, then use that to link with your news table:

    PHP Code:
    $query "SELECT `category_id`, `category_name` FROM `category`";
    $result mysql_query($query);
    if (!
    $result) { die($query."<br />".mysql_error()); }

    '<select name="category">';
    $row mysql_fetch_assoc($result))
    '<option value="'.$row['category_id'].'">'.$row['category_name'].'</option>';
    Now, when you add the above to your form, $_POST['category'] will have the id for the category, you just use that as the category_id field in your news table.
    Last edited by Burhan; 06-29-2005 at 02:30 AM.
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

  9. #9
    thank you for the tip fyre, i shall go test it out

  10. #10
    I got it to work correctly, thank you frye.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts