Announcement

Collapse
No announcement yet.

PHP and MySQL Site Design

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • PHP and MySQL Site Design

    I am learning both of the above for a friend to take their static HTML site and convert it to something dynamic both to improve the user friendliness and also to ease updating. So I have a few questions.

    Global Elements
    Certain parts of the site will simply be the same on every page - eg, the nav bar, header and footer etc. Can I simply take the existing HTML for this, place it into a table within MySQL and then have a global.php with something like $header = mysql_query... that links to the exact part in the table that contains the header HTML, then just use includes to put this in all pages and call the header with

    PHP Code:
    echo $header 
    Or is that too simple?

    This would of course mean that to update the one site, I only use a single query, rather than go round editing (what would be) close to 300 pages of HTML.

    Searching
    Is there a simple way to set up a search page to search a particular table (containing show episode details) by any specified query? Think I have a reasonable one going just now. Or, if the search is to be simple, searching only one part of a table (named "epKeywords"), how should I format the keywords? It's been suggested that I use the SQL command "LIKE" - though is there one that is more closely suited to "CONTAINS" - eg, return all results where specified table column "contains" X?

  • #2
    (1) You're almost there.
    PHP Code:
    list($header) = mysql_fetch_array(mysql_query('SELECT template FROM template WHERE title="header"')); 
    Should do the trick. Remember that if you go inside functions you must globalize $header:
    PHP Code:
    global $header
    (2) If you're using MySQL 4, I suggest using its built in FULLTEXT search engine:
    http://www.mysql.com/doc/F/u/Fulltex...46d1f131c82497
    You can only use it with MySQL 3, but you then won't have ability to search in a boolean way.

    Using LIKE is not recommended on large tables, but since you asked:
    Code:
    SELECT * FROM tblName WHERE field LIKE "%word%";
    Will return all records where the field column contains "word".
    Chen Avinadav
    Better to remain silent and be thought a fool than to speak out and remove all doubt.

    גם אני מאוכזב מסיקור תחרות לתור מוטור של NRG הרשת ע"י מעריב

    Comment


    • #3
      OK, think I am with you. So, for example. If I had index.php which would obviously need to borrow some of the global elements, I would first create global.php which had all the necessary queries in it, done using

      PHP Code:
      list($header
      as you pointed out. Then I would add statements in global.php such as

      PHP Code:
      global $header
      And then, and only only then could I call them in index.php by first including global.php in index.php and then using

      PHP Code:
      echo $header 
      to insert at that point the HTML from the field queried by $header?

      (Oh, and thanks for your help in this, I really appreciate it.)

      Comment


      • #4
        Correct, but you don't need this:
        PHP Code:
        global $header
        In global.php.

        You only need to use that if you are inside a function, for example:
        PHP Code:
        function showError($message) {
            global 
        $header$footer// If you have a footer template as well...

            
        echo $header $message $footer;

        (for example)
        Chen Avinadav
        Better to remain silent and be thought a fool than to speak out and remove all doubt.

        גם אני מאוכזב מסיקור תחרות לתור מוטור של NRG הרשת ע"י מעריב

        Comment


        • #5
          Ah yes, and now slowly the memories of my attempt to learn C++ come back to me... Thanks Firefly, really appreciate it.

          Comment


          • #6
            OK, so to continue the saga...

            I got a new HTML "Template site" to convert to PHP and MySQL. So basically, the chap was kind enough to give me the HTML with lots of explanatory tags - eg

            <--Main Header Content, same on every page-->
            BLAH BLAH BLAH
            <--End of Main Header-->

            etc.

            So I have divided up the Global elements of HTML and placed them into a table called "Global" in the site database. What I had intended to do was to set up a "functions.php" file that would be included (ie include(path.to/functions.php); ) in pages that would include the necessary database info and queries to extract these elements from the database and then to simply call them as needed on any page with the echo command.

            So, what I have at the mo is...

            functions.php -

            PHP Code:
            <?php
            // database configuration
            $host "localhost";
            $user "SQLuser";
            $pass "SQLpass";
            $sitedb "Databasename";

            //Universal Content Functions.
            $connect =mysql_connect("$host""$user""$pass");
            mysql_select_db("$sitedb",$connect);
            $header=mysql_query("SELECT content FROM global WHERE use=header",$connect);
            $navbar=mysql_query("SELECT content FROM global WHERE use=navbar",$connect);
            $contentbegins=mysql_query("SELECT content FROM global WHERE use=contentbegins",$connect);
            ?>
            and the calling file - index.php -

            PHP Code:
            <?php
            // includes
            include("siteconf/functions.php");
            echo 
            $header;
            ?>
            Which, sadly, returns the standard blank plain white page. No errors, SQL or PHP, just blank nothingness. Please help a guy out?

            Comment


            • #7
              This is how your file shold look like:
              PHP Code:
              <?php

              // Database configuration
              $host 'localhost';
              $user 'SQLuser';
              $pass 'SQLpass';
              $sitedb 'Databasename';

              // Universal Content Functions.
              $connect mysql_connect($host$user$pass);
              mysql_select_db($sitedb$connect);
              unset(
              $pass); // For security reasons

              // Get our templates
              $getheader mysql_query('SELECT content FROM global WHERE use="header"'$connect);
              $getnavbar mysql_query('SELECT content FROM global WHERE use="navbar"'$connect);
              $getcontentbegins mysql_query('SELECT content FROM global WHERE use="contentbegins"'$connect);

              $header $getheader['content'];
              $navbar $getnavbar['content'];
              $contentbegins $getcontentbegins['content'];

              ?>
              But if you want it shorter:
              PHP Code:
              <?php

              // Database configuration
              $host 'localhost';
              $user 'SQLuser';
              $pass 'SQLpass';
              $sitedb 'Databasename';

              // Universal Content Functions.
              $connect mysql_connect($host$user$pass);
              mysql_select_db($sitedb$connect);
              unset(
              $pass); // For security reasons

              // Get our templates
              list($header) = mysql_fetch_array(mysql_query('SELECT content FROM global WHERE use="header"'$connect));
              list(
              $navbar) = mysql_fetch_array(mysql_query('SELECT content FROM global WHERE use="navbar"'$connect));
              list(
              $contentbegins) = mysql_fetch_array(mysql_query('SELECT content FROM global WHERE use="contentbegins"'$connect));

              ?>
              But if you want it to look even better... (and easier to customize):
              PHP Code:
              <?php

              // Database configuration
              $host 'localhost';
              $user 'SQLuser';
              $pass 'SQLpass';
              $sitedb 'Databasename';

              // Universal Content Functions.
              $connect mysql_connect($host$user$pass);
              mysql_select_db($sitedb$connect);
              unset(
              $pass); // For security reasons

              // This array contains the name of the templates we want to get
              $tofetch = array(
                  
              'header',
                  
              'navbar',
                  
              'contentbegins'
              );

              // Walk through the array and fetch one template at a time
              foreach ($tofetch as $tempname) {
                  list($
              $tempname) = mysql_fetch_array(mysql_query("SELECT content FROM global WHERE use='$tempname'"$connect));
              }

              ?>
              And the ultimate way, which is the most efficient and easy to use is:
              PHP Code:
              <?php

              // Database configuration
              $host 'localhost';
              $user 'SQLuser';
              $pass 'SQLpass';
              $sitedb 'Databasename';

              // Universal Content Functions.
              $connect mysql_connect($host$user$pass);
              mysql_select_db($sitedb$connect);
              unset(
              $pass); // For security reasons

              // This array contains the name of the templates we want to get
              $tofetch = array(
                  
              'header',
                  
              'navbar',
                  
              'contentbegins'
              );

              // Get the templates from the database
              $templatelist implode("', '"$tofetch);
              $templates mysql_query("SELECT content, use FROM global WHERE use IN ('$templatelist')"$connect);

              while (
              $template mysql_fetch_array($templates) {
                  ${
              $template['use']} = $template['content'];
              }

              ?>
              Chen Avinadav
              Better to remain silent and be thought a fool than to speak out and remove all doubt.

              גם אני מאוכזב מסיקור תחרות לתור מוטור של NRG הרשת ע"י מעריב

              Comment


              • #8
                In order of the four solutions put forward, here are my results...

                First One
                Produces the same blank white no errors etc page that my code did.

                Second One
                Warning: Supplied argument is not a valid MySQL result resource in c:\apache\htdocs\sg1\siteconf\functions.php on line 15

                Warning: Supplied argument is not a valid MySQL result resource in c:\apache\htdocs\sg1\siteconf\functions.php on line 16

                Warning: Supplied argument is not a valid MySQL result resource in c:\apache\htdocs\sg1\siteconf\functions.php on line 17

                Third One
                Warning: Supplied argument is not a valid MySQL result resource in c:\apache\htdocs\sg1\siteconf\functions.php on line 23

                Warning: Supplied argument is not a valid MySQL result resource in c:\apache\htdocs\sg1\siteconf\functions.php on line 23

                Warning: Supplied argument is not a valid MySQL result resource in c:\apache\htdocs\sg1\siteconf\functions.php on line 23


                Fourth One
                Parse error: parse error in c:\apache\htdocs\sg1\siteconf\functions.php on line 25

                Comment


                • #9
                  Try this:
                  PHP Code:
                  <?php

                  // Database configuration
                  $host 'localhost';
                  $user 'SQLuser';
                  $pass 'SQLpass';
                  $sitedb 'Databasename';

                  // Universal Content Functions.
                  $connect mysql_connect($host$user$pass);
                  mysql_select_db($sitedb$connect);
                  unset(
                  $pass); // For security reasons

                  // This array contains the name of the templates we want to get
                  $tofetch = array(
                      
                  'header',
                      
                  'navbar',
                      
                  'contentbegins'
                  );

                  // Get the templates from the database
                  $templatelist implode("', '"$tofetch);
                  $templates mysql_query("SELECT content, use FROM global WHERE use IN ('$templatelist')"$connect);

                  while (
                  $template mysql_fetch_array($templates)) {
                      ${
                  $template['use']} = $template['content'];
                  }

                  ?>
                  If that doesn't work, the templates are not in the database.
                  Chen Avinadav
                  Better to remain silent and be thought a fool than to speak out and remove all doubt.

                  גם אני מאוכזב מסיקור תחרות לתור מוטור של NRG הרשת ע"י מעריב

                  Comment


                  • #10
                    OK, getting this:


                    Warning: Supplied argument is not a valid MySQL result resource in c:\apache\htdocs\sg1\siteconf\functions.php on line 25
                    Doesn't that say there's something wrong with the structure of the query? Or is it saying the info in the database returned by tehe query is not valid. I've attached a gif to show you how I've ordered the tables and fields, maybe that will shed some light on the issue.

                    Sorry if this issue is trying your patience (God knows it's driving me mad) - I appreciate any help you can offer.

                    Comment


                    • #11
                      Make sure the database, username and password are correct...
                      Chen Avinadav
                      Better to remain silent and be thought a fool than to speak out and remove all doubt.

                      גם אני מאוכזב מסיקור תחרות לתור מוטור של NRG הרשת ע"י מעריב

                      Comment


                      • #12
                        Yeah, that's always the first thing I do, though if they weren't, we'd get that "Unable to connect" error. If you want, I can set the database and php files somewhere and give you user/passes to look at it?

                        Comment


                        • #13
                          put die(mysql_error()); after the mysql_query function to see the exact SQL error that's returned:

                          PHP Code:
                          mysql_query("select something from somewhere") or die(mysql_error()); 
                          That would return the MySQL error, not the PHP error that you're getting

                          Comment


                          • #14
                            Thanks for that. I'll try it and report back. Was wondering myself if the actual HTML chars like " and / would be confusing the matter, though I suppose if you can use them in vBulletin posts then later recall them, PHP and MySQL can't really have a problem with them.

                            Comment


                            • #15
                              layout_oop.php:

                              Code:
                              $Layout = new layout;
                              
                              class layout{
                              
                              var $site = array();
                              
                              function layout(){
                              
                              $query = mysql_query("SELECT use, content FROM global");
                              
                              while( $temp = mysql_fetch_array( $query ) ){
                              $this->site[$temp['use']] = $temp['content'];
                              }
                              
                              $this->date = date("F j, Y, g:i a");
                              $this->IP = getenv("REMOTE_ADDR");
                              }
                              }

                              index.php:
                              Code:
                              require("./layout_oop.php");
                              
                              echo <<<EOF
                              
                              <html>
                              {$Layout->site['header']}
                              
                              {$Layout->date}
                              some text wotever
                              {$Layout->IP}
                              
                              {$Layout->site['navbar']}
                              
                              </html>
                              
                              EOF;


                              obviously now u can make calls to any functions/variables/classes you want and include them
                              Last edited by megahard; Thu 11 Jul '02, 5:15pm.

                              Comment

                              Loading...
                              Working...
                              X