Database forum FAQ + Bookmarked threads
Results 1 to 9 of 9

Thread: Database forum FAQ + Bookmarked threads

  1. #1
    WARNING: I am watching u! jayant's Avatar
    Join Date
    Aug 2002
    Location
    New Delhi, India
    Posts
    886

    Database forum FAQ + Bookmarked threads

    Following are a few bookmarked links to the most common questions on this forum.
    • Register Globals : Let the script behave as if register_globals is on even if it is off.
    • Undefined function: mysql_connect() : or Undefined function: mysql_pconnect() etc.
    • Getting MySQL to work with PHP 5 : Do I need to say anything else
    • "Supplied argument is not a valid MySQL result resource"? As the manual says, mysql_query returns FALSE if a SELECT query caused an error. FALSE is not a result resource, and trying to use it as one in mysql_fetch_array, mysql_num_rows or something like that will give you this error message. Just search these forums for "supplied argument is not a valid MySQL result resource" for plenty of examples and suggestions to use mysql_error to help diagnose what was wrong with the query. And read those manual pages.

    Links

    If you feel that we missed out some. Just PM moderators of this forum.
    All replies to this thread when this thread is unlocked/being edited will be deleted.

    When you have got your problem solved please click the "Mark Thread Resolved" at the bottom of the thread. This makes it easier for people go to threads where there are more chances of them finding a working solution.


    Do's / Don'ts
    1) The General overview:
    If it's improper to say something in a public place comprised of mixed races and ages, it's inappropriate to post it here in the forums.

    2) Advertising on the board:
    We can't allow this in any form. If someone is interested in getting exposure through the site, please contact the administrator via email at staff@phpbuilder.com . Footers may contain a pitch - it's a bit of a trade-off. The user is contributing to the community and in return they get exposure via their footer. Posting useless stuff and a huge ad in their footers is a complete NO.

    3) Off topic posts:
    Please use the Echo Lounge forum.

    4) Footer size:
    We need to limit this to 5 lines. Obviously if there are 20 posts on a page and we allow more, we'll end up with 100 extra lines to scroll when reading messages.

    5) Job posts
    PHPBuilder.com have a contract with Dice that doesn't allow them to host job openings. There are no exceptions to this.

    [edited to change the MySQL manual link - P]
    Last edited by Weedpacket; 03-26-2013 at 03:41 PM.
    Jayant Kumar Gandhi
    Computer Help | GZip/ Page Compression Test | My pic/ How I look?
    Marking the thread as 'resolved' is more important than saying "thanks. that solved it"
    Click "Mark Thread Resolved" in the thread tools and the top of the thread.
    Keep sensible thread titles.

  2. #2
    Senior Member
    Join Date
    Nov 2002
    Location
    at work
    Posts
    2,374
    Great Post! Just a couple additions that I've seen asked MANY times.

    If you ever have a problem with MySQL. Eg: Won't insert or update, nothing happens, etc. Here is the two best ways I have found to error check any MySQL problem.

    Create two variables, one that holds the statement you want to execute, and a seperate one to actualy execute it using mysql_query()
    PHP Code:
    $string "INSERT INTO table (first, last, email) VALUES('$first', '$last', '$email')";
    $query mysql_query($string); 
    Now, to do the error checking, print out the string to be executed so you can see what MySQL is ACTUALLY executing. Then, use mysql_error() along with mysql_query() for error checking.
    PHP Code:
    $string "INSERT INTO table (first, last, email) VALUES('$first', '$last', '$email')";

    print 
    $string "<br>";
    $query mysql_query($string) or die(mysql_error()); 
    That should help you solve any MySQL problem. Also, watch the use of reserved words. Make sure you're not using datatypes or built-in MySQL functions for column names.

    Inserting current time/date into MySQL

    NOW()

    That is MySQL's built in function for inserting the current date/time. Create a date or timestamp field for example, then to insert the current date/time, use
    PHP Code:
    $query mysql_query("INSERT INTO table (inputed_time) VALUES(NOW())") or die(mysql_error()); 
    Notice how I didn't create two seperate variables. If I did it like this:
    PHP Code:
    // This is the wrong way
    $string "INSERT INTO table (inputed_time) VALUES(NOW())";
    $query mysql_query($string) or die(mysql_error()); 
    It wouldn't work. NOW() is not a PHP function; it needs to be executed as a mysql function, thus, the first way is the correct way to do it.

    How to retrieve and display data from database

    Once you have your table setup and information is stored in it, all you do to put the information on the browser is query the database then display it.

    SELECT

    Here's an example:
    PHP Code:
    // replace these with your credentials
    $server "localhost";
    $user "cgraz";
    $password "phpb";
    $database "contacts";

    // Connect to Server
    mysql_connect($server$user$pass) or die(mysql_error());

    // Select Database
    mysql_select_db($database) or die(mysql_error());

    // Query the Database
    $query mysql_query("SELECT * FROM table"); // replace table with your table name

    while($row mysql_fetch_array($query)) { // put results into an array
       
    echo "Name: " $row["name"] . "<br>"// (assuming field name in db is 'name'
       
    echo "Email: " $row["email"] . "<br>"// (assuming field name in db is email)

    // and so forth

    // close the while loop

    And that's it!

    Cgraz
    Last edited by bradgrafelman; 11-15-2011 at 11:18 AM. Reason: fixed typo w/ code comment

  3. #3
    Senior Member
    Join Date
    Nov 2002
    Location
    at work
    Posts
    2,374
    My these are popular. For all of you looking for Next Previous (pagination) Script, here it is
    PHP Code:
    <? 

    /*  Replace the following credentials with yours */ 

    $server "localhost"
    $username "user"
    $password "pass"
    $database "your_db"
    $table "your_table"


    // Connect To MySQL Server 
    @mysql_connect($server$username$password) or die("Couldn't Connect to Database"); 

    // Select Database 
    @mysql_select_db($database) or die("Couldn't Select Database"); 

    // set number of results to display per page (in this case, 10 per page)
    $pagelimit "10"

    // run query
       
    $strSQL mysql_query("SELECT * FROM $table") or die(mysql_error()); 

    // count number of matches 
       
    $totalrows mysql_num_rows($strSQL); 

    // determine how many pages there will be by using ceil() and dividing total rows by pagelimit 
       
    $pagenums ceil ($totalrows/$pagelimit); 

    // if no value for page, page = 1 
        
    if ($page==''){ 
            
    $page='1'
        } 
    // create a start value 
        
    $start = ($page-1) * $pagelimit

    // blank matches found 
    echo "<b>" $totalrows " matches found</b><br>\n"

    // Showing Results 1 to 10 (or if you're page limit were 15) 1 to 15, etc. 
    $starting_no $start 1

    if (
    $totalrows $start $pagelimit) { 
       
    $end_count $totalrows
    } elseif (
    $totalrows $start >= $pagelimit) { 
       
    $end_count $start $pagelimit


       
    echo 
    "Results $starting_no to $end_count shown.<br>\n"

    // create dynamic next, previous, and page links 

    /* lets say you're set to show 10 results per page and your script comes out with 12 results. 
    this will allow your script to say next 2 if you're on the first page and previous 10 if you're on the second page. */ 

    if ($totalrows $end_count $pagelimit) { 
       
    $var2 $pagelimit
    } elseif (
    $totalrows $end_count <= $pagelimit) { 
       
    $var2 $totalrows $end_count


    $space "&nbsp;"

    // previous link (if you're on any page besides the first, create previous link)
    if ($page>1) { 
            echo 
    " <a href='" $PHP_SELF "?page=".($page-1)."' class=main>Previous" $space $pagelimit "</a>" $space $space ""
        } 

    // dynamic page number links

        
    for ($i=1$i<=$pagenums$i++) { 
            if (
    $i!=$page) { 
                echo 
    " <a href='" $PHP_SELF "?page=$i' class=main>$i</a>"
            } 
            else { 
                echo 
    " <b>[".$i."]</b>"
            } 
        } 


    // next link (if the page you are on is less than the total amount of page numbers, there are more pages left)

        
    if ($page<$pagenums) { 
            echo 
    "" $space $space $space $space " <a href='" $PHP_SELF "?page=".($page+1)."' class=main>Next " $var2 "</a> "
        } 

    /* output your data wherever you'd like. 

    BUT 

    in order for this all to work, before outputting your data, you have to run the query over using MySQL's LIMIT. 
    This will limit how many results are actually displayed on the page. */ 

       
    $strSQL mysql_query("SELECT * FROM $table LIMIT $start,$pagelimit") or die(mysql_error()); 

    // LIMIT 0,10 will start at 0 and display 10 results 
    // LIMIT 10,5 will start at 10 and display 5 results 

    /* now you can do whatever you'd like with this query. it will only output ten results per page. 
    change the $pagelimit variable to whatever to output more than 10 result per page. */ 


    ?>
    There are plenty of ways to do this; this is just my way. It's all commented so make sure you understand how it works before copying and pasting.

    Cgraz

  4. #4
    Senior Member
    Join Date
    Jun 2002
    Posts
    140
    Yikes! Never, ever use a *_numrows() call when you are simply finding the number of rows. Use SELECT COUNT( * ) FROM table instead, it is far more efficient.

  5. #5

  6. #6
    Missing in Action LordShryku's Avatar
    Join Date
    Aug 2002
    Location
    Occupational Hypnotherapy
    Posts
    7,227
    MySQL's Reserved Words.

    Learn them.
    Love them.
    Stop using them as field names please.

  7. #7
    Senior Member Tekime's Avatar
    Join Date
    Aug 2002
    Location
    Portland, ME
    Posts
    116

    Post A few things that have helped me along the way

    Preset table names
    Use variables or definitions in database queries instead of hard-coding table names.
    PHP Code:
    define('TBL_SESSIONS''tbl_sessions');
    $result mysql_query('SELECT * FROM ' TBL_SESSIONS
    Use Prefixes
    Prefix table and field names with short, meaningful identifiers. For example:

    tbl_config
    configId
    cfgProfileId
    cfgName
    cfgValue

    Use a consistent naming scheme
    Whether your fields are called cfgProfileId or cfg_profile_id, stick to that convention with the rest of your fields. (Same for table names)

    Always backup!!

  8. #8
    Senior Member Jason Batten's Avatar
    Join Date
    Mar 2005
    Location
    Australia
    Posts
    841
    I ran into a little trouble today when upgrading from MySQL 4.0 to MySQL 5. As of MySQL 4.1 the way the PASSWORD() function works has changed, see the manual for more details

    The following will cause an error on MySQL 4.1 and above:
    PHP Code:
    $query mysql_query("
    SELECT username, password
    FROM admin_table
    WHERE username = '
    $user'
    AND
    password = PASSWORD('
    $pass')"); 
    Instead you must simply use the function OLD_PASSWORD() which funny enough does what the old PASSWORD() function use to do.
    PHP Code:
    $query mysql_query("
    SELECT username, password
    FROM admin_table
    WHERE username = '
    $user'
    AND
    password = OLD_PASSWORD('
    $pass')"); 
    As a lot of online tutorials & books still demonstrate using the PASSWORD() function I thought it would be wise to post this here. I hope this prevents someone from locking themselves out of their own script Naturally I would only suggest using this as a temporary fix until you upgrade the whole of your security.
    a new day, a new beginning

  9. #9
    Junior Member 8ta8ta's Avatar
    Join Date
    Aug 2006
    Posts
    22
    Last edited by 8ta8ta; 08-23-2006 at 01:40 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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