Help with querys please?

luny

I've been messing with this for hours with no success.

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

DROP TABLE IF EXISTS news;
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?

Any tips or suggestions would be appreciated.

Thanks for the help 🙂

sneakyimp

if you have a form

then when the user clicks the 'submit' button, the form is POSTed to the file located in the same location on your server named 'my_script.php'.

inside my_script.php, you can do something like this:

<?
// my_script.php

echo $_POST['my_text_input'];
?>

if $_POST doesn't work, try $HTTP_POST_VARS.

ALSO, if the user has entered any quote marks, you will need to make sure your query doesn't get broken by them. some PHP installations are configured to automatically put a backslash in front of any quote marks in data from a form. yours may or may not be.

play around with it and see if you can get the data from the form into your variables in your script.

as for the queries, try coding something...it would be easier to help you if we saw some code.

bradgrafelman

VALUES ('$id',

Integers are not surrounded in quotes in just about any language. Though I'm not sure that this will cause MySQL to act different, you might want to change that for style/compatibilty/whatever reasons.

sneakyimp

i think mySQL will work just fine with the quotes.

luny

I redid my tables and decided to make:

DROP TABLE IF EXISTS news;
CREATE TABLE news (
id int(10) unsigned NOT NULL auto_increment,
category_id bigint(20) NOT NULL,
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 category;
CREATE TABLE category (
category_id bigint(20) NOT NULL auto_increment,
category_name char(80) default NULL,
PRIMARY KEY (category_id)
) TYPE=MyISAM;

I got the news to post in categories I just dont know how to display it. Heres the code for displaying the news.

<?php
/ 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']}\">" .
"<b>C$comment_row[0]mments</a> )</div></b></p>\n";

/ 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";
return;
}
$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 /
displayComments($id);
}

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"<BR>\n";
echo"<b>Comment</b>:\n";
echo"<BR>\n";
echo "<TEXTAREA cols=\"40\" rows=\"3\" " .
"name=\"comment\"></TEXTAREA><BR>\n";
echo"<BR>\n";
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 " .
"VALUES('',$id,'{$POST['name']}','{$POST['site']}','{$_POST['comment']}')";
mysql_query($query);

echo "Comment entered. Thanks!<BR>\n";
echo "<a href=\"{$_SERVER['PHP_SELF']}" .
"?action=show&id=$id\">Back</a>\n";
}

/ this is where the script decides what do do /

switch($_GET['action']) {

case 'show':
displayOneItem($GET['id']);
break;
case 'all':
displayNews(1);
break;
case 'addcomment':
addComment($GET['id']);
break;
default:
displayNews();
}
?>

and here is the code for posting news:

<?php

include("config.php");

if($submit)

{//begin of if($submit).

  // Set global variables to easier names

 // and pervent sql injection and apostrophe to break the db.

  $id = mysql_real_escape_string($_POST['id']);

  $title = mysql_real_escape_string($_POST['title']);

  $category_id = mysql_real_escape_string($_POST['category_id']);

  $newstext = mysql_real_escape_string($_POST['newstext']);



          //check if (title) field is empty then print error message.

          if(!$title){  //this means If the title is really empty.

                 echo "Error: News title is a required field. Please fill it.";

                 exit(); //exit the script and don't do anything else.

          }// end of if



     //run the query which adds the data gathered from the form into the database

     $result = mysql_query("INSERT INTO news (id, postdate, title, category_id, newstext)

                   VALUES ('$id',NOW(),'$title','$category_id','$newstext')",$connect);


  // Set global variables to easier names

 // and pervent sql injection and apostrophe to break the db.
          //check if (title) field is empty then print error message.

          if(!$category_id){  //this means If the title is really empty.

                 echo "Error: categoryID is a required field. Please fill it.";

                 exit(); //exit the script and don't do anything else.

          }// end of if

  $category_id = mysql_real_escape_string($_POST['category_id']);

  $category_name = mysql_real_escape_string($_POST['category_name']);

$result = mysql_query("INSERT INTO category (category_id, category_name)

VALUES ('$category_id','$category_name')",$connect);

mysql_query($query);

      echo "<b><center>News added Successfully!<br>You'll be redirected to index after (4) Seconds</center>";

      echo "<meta http-equiv=Refresh content=4;url=index.php>";

}//end of if($submit).

// If the form has not been submitted, display it!

else

{//begin of else

  ?>

  <br>

  <form method="post" action="<?php echo $PHP_SELF ?>">

Title</br>
<input type="text" name='title'></textarea>
<br>
Category ID</br>
<input type="text" name='category_id'></textarea>
<br>
Category</br>
<input type="text" name='category_name'></textarea>
<br>
News<br>
<textarea name='newstext' rows="7" cols="30"></textarea>

  <br>

  <input type="submit" name="submit" value="Add News">

  </form>

</center>
<?

}//end of else

So, to recap. I would just like to be able to post news in categories, news can belong only in one category with the way i have the tables set up, and that is fine.

I'd also like users to be able to click a link and it specifically shows them just that category of news. (I assume i can do that part an array and seperiate queries)

cheese

Hi Luny,

Have a look at www.cornardunited.co.uk and look at the news section. Is that the kind of thing you are looking for?

If so drop me a line and I can help with some code from the site.

Cheers,

Neil.