By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,433 Members | 776 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,433 IT Pros & Developers. It's quick & easy.

How to make my php/msql calls more efficient?

P: 1
Hi, I'm pretty new to the php/mysql world and am building an article database for a website with multiple content types. I have an entry screen built that allows the site owner to enter articles, set article types, and set articles as features. Each page in the site calls the database for a feature article for that pages content type, then calls again for the 5 newest articles of that content type excluding any article that is identified as a feature.

I have this mostly working but it is just kind of hacked together. My question is how do I make this more efficient? Code is below

The Submit screen:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. include(login file);
  3. $link = mysql_connect($hostname, $user, $password);
  4. mysql_set_charset("joshai_article",$link);
  5. $db_selected = mysql_select_db('joshai_article', $link);
  6. if (!$db_selected) { die ('Database access error : ' . mysql_error());}
  7. // Validate this user
  8. $test_username = $_POST['test_username'];
  9. $query = "SELECT password
  10.           FROM login
  11.           WHERE username = '$test_username'";
  12. $result = mysql_query($query);
  13. if (mysql_num_rows($result) != 1) {
  14.   echo "Something is wrong";
  15.   exit;
  16. }
  17. $password_row = mysql_fetch_array($result);
  18. $db_password = $password_row[0];
  19.  
  20. if ($_POST['test_password'] == $db_password &&
  21. $_POST['test_password'] != "") {
  22.   if ($_POST['Submit'] == 'Enter') {
  23.     // Enter new entry
  24.     $date = date('Ymd'); // Remember, date is an integer type
  25.     $article = $_POST['article'];
  26.     $author = $_POST['author'];
  27.     $byline = $_POST['byline'];
  28.     $title1 = $_POST['title'];
  29.     $title = addslashes($title1);
  30.     $feature = ($_POST['chkfeature'] == 1) ? "1" : "0";
  31.     switch($_POST['type']){
  32.             case '1':
  33.                $type = '1';
  34.                break;
  35.             case '2':
  36.                $type = '2';
  37.                break;
  38.             case '3':
  39.                $type = '3';
  40.                break;
  41.             case '4';
  42.             $type = '4';
  43.             break;
  44.             case '5';
  45.             $type = '5';
  46.             break;
  47.             default:
  48.                $type = '0';
  49.          }
  50.  
  51.     $query = "INSERT INTO content (ID, date, title, author, article, byline, approved, feature)
  52. VALUES(null, $date, '$title', '$author', '$article', '$byline','1', '$feature')";
  53. mysql_query($query) OR die(mysql_error());
  54. $article_ID = mysql_insert_id();    
  55. $query = "INSERT INTO content_type (ID, type_ID, article_ID) VALUES (NULL,$type,$article_ID)";
  56.  
  57.     $result = mysql_query($query);
  58.  
  59.     if (mysql_affected_rows() == 1) {
  60.       header("Location: db_login.php");
  61.     } else {
  62.       echo "There was a problem inserting your text.";
  63.       exit;
  64.     }
  65.   } else {
  66.     // Show the form
  67.     $php_self = $_SERVER['PHP_SELF'];
  68.     $test_password = $_POST['test_password'];
  69. $form_str = <<< EOFORMSTR
  70.  
  71. <HTML>
  72. webform goes here</html>
  73. ?>
The code for displaying content in content div of page

Expand|Select|Wrap|Line Numbers
  1. <?  // Open database connection
  2. include("login file");
  3. mysql_connect($hostname, $user, $password);
  4. mysql_select_db("joshai_article");
  5.  
  6. // Identify the featured entry for this section
  7. $entropia= "SELECT content.ID, content.title, content.author, content.byline, content.article FROM content, content_type, subject
  8. WHERE content.ID = content_type.article_ID
  9. AND feature=1
  10. AND content_type.type_ID = subject.type_ID
  11. AND subject.type_ID = 5
  12. AND content.approved = '1'";
  13. // Print the feature article at top of content div
  14. $result1= mysql_query($entropia)
  15. or die("dude you screwed up");
  16. while (list($link, $title, $author, $byline, $article1)=
  17. mysql_fetch_array($result1)){
  18. $article=stripslashes($article1);
  19.  
  20. echo "<h2>$title </h2>";
  21. echo "<h4>$author </h4>";
  22. echo "$byline <br>";
  23. echo nl2br("$article");
  24.  
  25.  //echo "<a href=\"view.php?id=". $link."\">View</a>";
  26. echo "<br><hr />";
  27.  
  28. // Identify the latest 5 articles for this page type while excluding any featured article
  29. $query = "SELECT content.ID, content.title, content.author, content.byline, content.article FROM content, content_type, subject WHERE content.ID = content_type.article_ID
  30. AND content_type.type_ID = subject.type_ID
  31. AND subject.type_ID = 5
  32. AND content.approved = '1' AND content.feature != '1' ORDER BY content.article_ID DESC LIMIT 5
  33.  
  34. ";
  35. $result = mysql_query($query)
  36. or die("something is wrong");
  37.  
  38. // print the latest 5 articles of this content type to the content div
  39.  
  40. while (list($link, $title, $author, $byline, $article1)=
  41. mysql_fetch_array($result)){
  42. $article=stripslashes($article1);
  43.  
  44. echo "<h2>$title </h2>";
  45. echo "<h4>$author </h4>";
  46. echo "$byline <br>";
  47. echo nl2br("$article");
  48.  
  49.  //echo "<a href=\"view.php?id=". $link."\">View</a>";
  50. echo "<br><hr />";
  51. ?>
I am trying to set up a code to grab an id from the page url to insert into the subject_type.ID field so I can just use an include to put this script in every page without having to manually go in and set the ID number for every new content category.

Any suggestions would really help this fish out of water.
Jan 3 '09 #1
Share this Question
Share on Google+
1 Reply


dlite922
Expert 100+
P: 1,584
@joshai
Your script is barely acceptable and could use a good organization and clean up. Consider classes or at least a database class.

what exactly do you need to grab an ID? You want a script that grabs a value from the URL parameter and puts it into a common variable? so you can include this in your scripts and access that variable without repeating the script? If this is the case, you need to meeting $_GET[], $_POSTS[] younger sister.

Let me know,



Dna
Jan 6 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.