How to make my php/msql calls more efficient?

joshai

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

 
<?php

include(login file);

$link = mysql_connect($hostname, $user, $password);

mysql_set_charset("joshai_article",$link);

$db_selected = mysql_select_db('joshai_article', $link);

if (!$db_selected) { die ('Database access error : ' . mysql_error());}

// Validate this user

$test_username = $_POST['test_username'];

$query = "SELECT password

          FROM login

          WHERE username = '$test_username'";

$result = mysql_query($query);

if (mysql_num_rows($result) != 1) {

  echo "Something is wrong";

  exit;

}

$password_row = mysql_fetch_array($result);

$db_password = $password_row[0];
 
if ($_POST['test_password'] == $db_password &&

$_POST['test_password'] != "") {

  if ($_POST['Submit'] == 'Enter') {

    // Enter new entry

    $date = date('Ymd'); // Remember, date is an integer type

    $article = $_POST['article'];

    $author = $_POST['author'];

    $byline = $_POST['byline'];

    $title1 = $_POST['title'];

    $title = addslashes($title1);

    $feature = ($_POST['chkfeature'] == 1) ? "1" : "0";

    switch($_POST['type']){

            case '1':

               $type = '1';

               break;

            case '2':

               $type = '2';

               break;

            case '3':

               $type = '3';

               break;

            case '4';

            $type = '4';

            break;

            case '5';

            $type = '5';

            break;

            default:

               $type = '0';

         }
 
    $query = "INSERT INTO content (ID, date, title, author, article, byline, approved, feature)

VALUES(null, $date, '$title', '$author', '$article', '$byline','1', '$feature')";

mysql_query($query) OR die(mysql_error());

$article_ID = mysql_insert_id();    

$query = "INSERT INTO content_type (ID, type_ID, article_ID) VALUES (NULL,$type,$article_ID)";
 
    $result = mysql_query($query);
 
    if (mysql_affected_rows() == 1) {

      header("Location: db_login.php");

    } else {

      echo "There was a problem inserting your text.";

      exit;

    }

  } else {

    // Show the form

    $php_self = $_SERVER['PHP_SELF'];

    $test_password = $_POST['test_password'];

$form_str = <<< EOFORMSTR
 
<HTML>

webform goes here</html>

?>

The code for displaying content in content div of page

Expand|Select|Wrap|Line Numbers

 
<?  // Open database connection

include("login file");

mysql_connect($hostname, $user, $password);

mysql_select_db("joshai_article");
 
// Identify the featured entry for this section

$entropia= "SELECT content.ID, content.title, content.author, content.byline, content.article FROM content, content_type, subject

WHERE content.ID = content_type.article_ID

AND feature=1

AND content_type.type_ID = subject.type_ID

AND subject.type_ID = 5

AND content.approved = '1'";

// Print the feature article at top of content div

$result1= mysql_query($entropia)

or die("dude you screwed up");

while (list($link, $title, $author, $byline, $article1)=

mysql_fetch_array($result1)){

$article=stripslashes($article1);
 
echo "<h2>$title </h2>";

echo "<h4>$author </h4>";

echo "$byline <br>";

echo nl2br("$article");
 
 //echo "<a href=\"view.php?id=". $link."\">View</a>";

echo "<br><hr />";

} 
 
// Identify the latest 5 articles for this page type while excluding any featured article

$query = "SELECT content.ID, content.title, content.author, content.byline, content.article FROM content, content_type, subject WHERE content.ID = content_type.article_ID

AND content_type.type_ID = subject.type_ID

AND subject.type_ID = 5

AND content.approved = '1' AND content.feature != '1' ORDER BY content.article_ID DESC LIMIT 5
 
";

$result = mysql_query($query)

or die("something is wrong");
 
// print the latest 5 articles of this content type to the content div
 
while (list($link, $title, $author, $byline, $article1)=

mysql_fetch_array($result)){

$article=stripslashes($article1);
 
echo "<h2>$title </h2>";

echo "<h4>$author </h4>";

echo "$byline <br>";

echo nl2br("$article");
 
 //echo "<a href=\"view.php?id=". $link."\">View</a>";

echo "<br><hr />";

} 

?>

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.

dlite922

Quote:

Originally Posted by joshai

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

 
<?php

include(login file);

$link = mysql_connect($hostname, $user, $password);

mysql_set_charset("joshai_article",$link);

$db_selected = mysql_select_db('joshai_article', $link);

if (!$db_selected) { die ('Database access error : ' . mysql_error());}

// Validate this user

$test_username = $_POST['test_username'];

$query = "SELECT password

          FROM login

          WHERE username = '$test_username'";

$result = mysql_query($query);

if (mysql_num_rows($result) != 1) {

  echo "Something is wrong";

  exit;

}

$password_row = mysql_fetch_array($result);

$db_password = $password_row[0];
 
if ($_POST['test_password'] == $db_password &&

$_POST['test_password'] != "") {

  if ($_POST['Submit'] == 'Enter') {

    // Enter new entry

    $date = date('Ymd'); // Remember, date is an integer type

    $article = $_POST['article'];

    $author = $_POST['author'];

    $byline = $_POST['byline'];

    $title1 = $_POST['title'];

    $title = addslashes($title1);

    $feature = ($_POST['chkfeature'] == 1) ? "1" : "0";

    switch($_POST['type']){

            case '1':

               $type = '1';

               break;

            case '2':

               $type = '2';

               break;

            case '3':

               $type = '3';

               break;

            case '4';

            $type = '4';

            break;

            case '5';

            $type = '5';

            break;

            default:

               $type = '0';

         }
 
    $query = "INSERT INTO content (ID, date, title, author, article, byline, approved, feature)

VALUES(null, $date, '$title', '$author', '$article', '$byline','1', '$feature')";

mysql_query($query) OR die(mysql_error());

$article_ID = mysql_insert_id();    

$query = "INSERT INTO content_type (ID, type_ID, article_ID) VALUES (NULL,$type,$article_ID)";
 
    $result = mysql_query($query);
 
    if (mysql_affected_rows() == 1) {

      header("Location: db_login.php");

    } else {

      echo "There was a problem inserting your text.";

      exit;

    }

  } else {

    // Show the form

    $php_self = $_SERVER['PHP_SELF'];

    $test_password = $_POST['test_password'];

$form_str = <<< EOFORMSTR
 
<HTML>

webform goes here</html>

?>

The code for displaying content in content div of page

Expand|Select|Wrap|Line Numbers

 
<?  // Open database connection

include("login file");

mysql_connect($hostname, $user, $password);

mysql_select_db("joshai_article");
 
// Identify the featured entry for this section

$entropia= "SELECT content.ID, content.title, content.author, content.byline, content.article FROM content, content_type, subject

WHERE content.ID = content_type.article_ID

AND feature=1

AND content_type.type_ID = subject.type_ID

AND subject.type_ID = 5

AND content.approved = '1'";

// Print the feature article at top of content div

$result1= mysql_query($entropia)

or die("dude you screwed up");

while (list($link, $title, $author, $byline, $article1)=

mysql_fetch_array($result1)){

$article=stripslashes($article1);
 
echo "<h2>$title </h2>";

echo "<h4>$author </h4>";

echo "$byline <br>";

echo nl2br("$article");
 
 //echo "<a href=\"view.php?id=". $link."\">View</a>";

echo "<br><hr />";

} 
 
// Identify the latest 5 articles for this page type while excluding any featured article

$query = "SELECT content.ID, content.title, content.author, content.byline, content.article FROM content, content_type, subject WHERE content.ID = content_type.article_ID

AND content_type.type_ID = subject.type_ID

AND subject.type_ID = 5

AND content.approved = '1' AND content.feature != '1' ORDER BY content.article_ID DESC LIMIT 5
 
";

$result = mysql_query($query)

or die("something is wrong");
 
// print the latest 5 articles of this content type to the content div
 
while (list($link, $title, $author, $byline, $article1)=

mysql_fetch_array($result)){

$article=stripslashes($article1);
 
echo "<h2>$title </h2>";

echo "<h4>$author </h4>";

echo "$byline <br>";

echo nl2br("$article");
 
 //echo "<a href=\"view.php?id=". $link."\">View</a>";

echo "<br><hr />";

} 

?>

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.

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

How to make my php/msql calls more efficient?

re: How to make my php/msql calls more efficient?

Similar PHP bytes

/bytes/development

/bytes/it

/bytes/about