Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New Coder
    Join Date
    Feb 2012
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts

    mysql_query in custom function

    Hi,
    Ive been learning php over the last year and I am starting to venture into functions to help keep my code clean an easier to manage.
    Every page on my site will have a heading, subheading and content(body) so i thought why not create a function which runs the query for that page and stores these into variables. however it doesnt seem to be working. my db_connect and db_close functions work but not cont_qry() any help greatly appreciated.

    PHP Code:
    <?php
    session_start
    ();
    function 
    db_connect() {
        global 
    $db_link;
        
        @
    $db_link mysql_connect('localhost''''');
        
        if (
    $db_link) @mysql_select_db('');
        return 
    $db_link;
      }

      function 
    db_close() {
        global 
    $db_link;
        
        
    $result mysql_close($db_link);
        
        return 
    $result;
      }
      
      function 
    cont_qry($NAME) {
            
    db_connect();
            
    $qry mysql_query("SELECT HEADING, SUBHEADING, BODY FROM page_content WHERE NAME='$NAME'");
            while(
    $row mysql_fetch_array($qry))
            {
                
    $HEADING $row['HEADING'];
                
    $SUBHEADING $row['SUBHEADING'];
                
    $BODY $row['BODY'];
             }
             
    mysql_free_result($qry);
             
    db_close();
             return 
    $HEADING;
             return 
    $SUBHEADING;
             return 
    $BODY;
       }
    ?>

  • #2
    New Coder
    Join Date
    Feb 2012
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I have figured it out now, for anyone else struggling here is my answer

    PHP Code:
    <?php
    session_start
    ();
    function 
    db_connect() {
        global 
    $db_link;
        
        @
    $db_link mysql_connect('''''');
        
        if (
    $db_link) @mysql_select_db('');
        return 
    $db_link;
      }

      function 
    db_close() {
        global 
    $db_link;
        
        
    $result mysql_close($db_link);
        
        return 
    $result;
      }
      
      function 
    cont_qry($NAME) {
            
    db_connect();
            @ 
    $qry mysql_query("SELECT HEADING, SUBHEADING, BODY FROM page_content WHERE NAME='$NAME'");
            while(
    $row mysql_fetch_array($qry))
            {
                global 
    $HEADING;
                global 
    $SUBHEADING;
                global 
    $BODY;
                
    $HEADING $row['HEADING'];
                
    $SUBHEADING $row['SUBHEADING'];
                
    $BODY $row['BODY'];
             }
             
    mysql_free_result($qry);
             
    db_close();
       }
    ?>

  • #3
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,215
    Thanks
    12
    Thanked 338 Times in 334 Posts
    there is no need to use $db_link as global. mysql_* functions automatically use the last open connection.

    but if you're at such a structure, consider using MySQLi or PDO objects over the outdated mysql extension.

    beside that, while the @ operator conveniently suppresses error messages on the production server, it hinders you in development (where you need those messages) and it slows PHP down. additionally there is no error handling which will sooner or later lead to the most common PHP/MySQL error and I also see nothing to prevent SQL Injections.
    Last edited by Dormilich; 02-29-2012 at 07:49 AM.
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #4
    New Coder
    Join Date
    Feb 2012
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for your input hadnt noticed anyone had replied.

    This is what I now have:
    PHP Code:
    <?php
    session_start
    ();
    function 
    db_connect() {
        
        
    $db_link mysql_connect();
        
        if (
    $db_link) @mysql_select_db('');
        return 
    $db_link;
      }

      function 
    db_close() {
            
        
    $result mysql_close($db_link);
        
        return 
    $result;
      }
      
      function 
    cont_qry($NAME) {
            
    db_connect();
            
    $NAME mysql_real_escape_string($NAME);
            
    $qry mysql_query("SELECT HEADING, SUBHEADING, BODY, META_DESC, META_KEYS, PAGE_TITLE FROM page_content WHERE NAME='$NAME'");
            
    $num_rows mysql_num_rows($qry);
            IF (
    $num_rows==0){
                
    header ("location: error.php");
            }
            while(
    $row mysql_fetch_array($qry))
            {
                global 
    $HEADING;
                global 
    $SUBHEADING;
                global 
    $BODY;
                global 
    $PAGE_TITLE;
                global 
    $META_KEYS;
                global 
    $META_DESC;
                
    $HEADING $row['HEADING'];
                
    $SUBHEADING $row['SUBHEADING'];
                
    $BODY $row['BODY'];
                
    $META_DESC $row['META_DESC'];
                
    $META_KEYS $row['META_KEYS'];
                
    $PAGE_TITLE $row['PAGE_TITLE'];
             }
             
    mysql_free_result($qry);
             
    db_close();
       }
    ?>

  • #5
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,215
    Thanks
    12
    Thanked 338 Times in 334 Posts
    marginally better.

    there is still no error handling (what if your query fails?) and there is still the issue with the globals. do not use globals! they are a nightmare to debug and maintain (what if some external function changes the value so your code will fail?). if there is absolutely no way around at least use the superglobal $GLOBALS.
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #6
    New Coder
    Join Date
    Feb 2012
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts
    im new to using functions but the in my php script i call this function and without declaring the variables as global I cannot use the values stored in the variables outside of the function.
    would this be done using return instead?

  • #7
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,215
    Thanks
    12
    Thanked 338 Times in 334 Posts
    you can pass in variables as parameters and get output parameters via return.

    PHP Code:
    function query($conn$data)
    {
        
    // prepare SQL string
        
    $sql = ... ;
        
    $res mysql_query($sql$conn);

        if (
    false === $res)
        {
            throw new 
    UnexpectedValueException("Error on SQL: '$sql'" PHP_EOL mysql_error());
        }
        
    // let another function handle the data fetching
        
    return $res;

    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • Users who have thanked Dormilich for this post:

    Philwn (03-05-2012)

  • #8
    New Coder
    Join Date
    Feb 2012
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I may be missing something(not fully understanding) here but i have merged your code into my function. I have then called the function cont_qry('about') and recieved a blank page, so i tried echo 4res and i get undefined variable. how could I use the information it returns without putting them in global variables?

    PHP Code:
    <?php
    session_start
    ();
    function 
    db_connect() {
        
        
    $db_link mysql_connect('localhost''''');
        
        if (
    $db_linkmysql_select_db('');
        return 
    $db_link;
      }

      function 
    db_close() {
            
        
    $result mysql_close();
        
        return 
    $result;
      }
      
      function 
    cont_qry($NAME) {
            
    db_connect();
            
    $NAME mysql_real_escape_string($NAME);
            
    $sql "SELECT HEADING, SUBHEADING, BODY, META_DESC, META_KEYS, PAGE_TITLE FROM page_content WHERE NAME='$NAME'";
            
    $res mysql_query($sql);
            if (
    false === $res)
                {
                    throw new 
    UnexpectedValueException("Error on SQL: '$sql'" PHP_EOL mysql_error());
                }
            
    // let another function handle the data fetching
            
    return $res;
            
    db_close();
       }
    ?>

    <!--test the function -->
    <?php cont_qry('about');
    echo 
    $res;
    ?>

  • #9
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,215
    Thanks
    12
    Thanked 338 Times in 334 Posts
    Quote Originally Posted by Philwn View Post
    I may be missing something(not fully understanding) here but i have merged your code into my function. I have then called the function cont_qry('about') and recieved a blank page, so i tried echo 4res and i get undefined variable. how could I use the information it returns without putting them in global variables?

    PHP Code:
    <!--test the function -->
    <?php cont_qry('about');
    echo 
    $res;
    ?>
    you need to assign the return value, otherwise it is dumped into the void. and since variables ain't accessible outside their Scope, you have an undefined variable there.
    PHP Code:
    $res cont_qry('about');
    var_dump($res); 
    PS. I'm not sure if it will work if you close the connection before you fetch the data.
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #10
    New Coder
    Join Date
    Feb 2012
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts
    var_dump just gives me
    Code:
    resource(3) of type (mysql result)

  • #11
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,215
    Thanks
    12
    Thanked 338 Times in 334 Posts
    that is supposed to be correct.
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer


  •  

    Posting Permissions

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