SitePoint Sponsor

User Tag List

Page 1 of 4 1234 LastLast
Results 1 to 25 of 84
  1. #1
    SitePoint Evangelist
    Join Date
    Jan 2002
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    I want to write a collector's database

    I'm writing my first php application. It's a simple collectors database, that I would like to use for my movie collection.

    I have created my database. The database consists of one table, with six feilds. What I want it to do is to display the results in a table.

    First of all, how do I get the script to connect to the database? I know I have to have a config file, to tell it the database name and password. I want this to be as simple as possible.

    Secondly, what is the code to display the text of each field in a table cell?

    I'll get to more advanced functions later, such as sorting the results by field.

  2. #2
    SitePoint Evangelist Aska's Avatar
    Join Date
    Aug 2003
    Location
    Melbourne, Australia
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Joshua, the first example on PHP.net's manual for mysql_fetch_assoc is probably what you are looking for.

  3. #3
    SitePoint Evangelist
    Join Date
    Jan 2002
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got the following error: Could not successfully run query (SELECT id as title, studio, year FROM dvd WHERE userstatus = 1) from DB: Unknown column 'id' in 'field list'

    This is what I have so far. Could someone please explain what the following code does?

    PHP Code:
    <?php
    $conn 
    mysql_connect("localhost""joshuasp_dvdlist""password" );

    if (!
    $conn) {
    echo 
    "Unable to connect to DB: " mysql_error();
    exit;
    }

    if (!
    mysql_select_db("joshuasp_dvdlist" )) {
    echo 
    "Unable to select dvdlist: " mysql_error();
    exit;
    }

    $sql "SELECT id as title, studio, year (Is this not where I put the field names?)
    FROM dvd (Is this not where I put the table name?)
    WHERE userstatus = 1"
    ;
    $result mysql_query($sql);
    if (!
    $result) {
    echo 
    "Could not successfully run query ($sql) from DB: " mysql_error();
    exit;
    }

    if (
    mysql_num_rows($result) == 0) {
    echo 
    "No rows found, nothing to print so am exiting";
    exit;
    }
    // While a row of data exists, put that row in $row as an associative array
    // Note: If you're expecting just one row, no need to use a loop
    // Note: If you put extract($row); inside the following loop, you'll
    // then create $userid, $fullname, and $userstatus
    while ($row mysql_fetch_assoc($result)) {
    echo 
    $row["title"]; (I also put Field names here.)
    echo 
    $row["studio"]; (Here.)
    echo 
    $row["year"]; (And Here.)
    }

    mysql_free_result($result);

    ?>

  4. #4
    SitePoint Evangelist Aska's Avatar
    Join Date
    Aug 2003
    Location
    Melbourne, Australia
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Joshua,

    From your code (and the error msg), I assume your table `dvd` contains these columns: 'title', 'studio' and 'year'? If so, pls try modifying the SQL query to:
    PHP Code:
    $sql "SELECT title, studio, year FROM dvd"
    (error message due to the use of column names that do not exist in your table: `id` and `userstatus`)

  5. #5
    SitePoint Evangelist
    Join Date
    Jan 2002
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Did that. It no longer has the parse error. Now, it says:

    Could not successfully run query (SELECT title, studio, year FROM DVD) from DB: Table 'joshuasp_dvdlist.DVD' doesn't exist
    Last edited by Joshua Clinard; Nov 3, 2003 at 09:32.

  6. #6
    PHP manual bot bronze trophy Gaheris's Avatar
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please, read the error message. Does the table DVD exist in the database joshuasp_dvdlist?

  7. #7
    SitePoint Evangelist
    Join Date
    Jan 2002
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Gaheris
    Please, read the error message. Does the table DVD exist in the database joshuasp_dvdlist?
    Of course. At least according to my knowledge of how to set up a table.

  8. #8
    PHP manual bot bronze trophy Gaheris's Avatar
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is DVD written in uppercase in your query, is it really uppercase in the database? I'm not sure if mysql cares about it though.

  9. #9
    SitePoint Evangelist
    Join Date
    Jan 2002
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Gaheris
    Is DVD written in uppercase in your query, is it really uppercase in the database? I'm not sure if mysql cares about it though.
    Movie Collection Script

    Thanks. That did it. I need to have each of these fields in a seperate table cell. And eatch row in the database should be a seperate row in the database. Is they a way I could write this as a function, so all I would have to do would be to call the function for each field? If not, what would be the best way to do this?

  10. #10
    PHP manual bot bronze trophy Gaheris's Avatar
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something like this?
    PHP Code:
    <?php
    // Connection information
    $db_host 'localhost';
    $db_user 'joshuasp_dvdlist';
    $db_pwd  'password';
    $db_name 'joshuasp_dvdlist';

    // Settings
    define('DEBUG'true);

    // Connecting
    $conn mysql_connect($db_host$db_user$db_pwd); 
    if (!
    $result) {
        
    $text = (DEBUG) ? 'Unable to connect to DB host: ' mysql_error()
                        : 
    'There has been an error, please contact our lovely administrator';
        die(
    $text);
    }

    $result mysql_select_db($db_name);
    if (!
    $result) {
        
    $text = (DEBUG) ? 'Unable to select DB \'' $db_name ': \'' mysql_error()
                        : 
    'There has been an error, please contact our lovely administrator';
        die(
    $text);
    }

    // Getting the data
    $sql 'SELECT `title`, `studio`, `year` FROM `dvd`';
    $result mysql_query($sql$conn);
    if (!
    $result) {
        
    $text = (DEBUG) ? 'Could not successfully run query ($sql): ' mysql_error()
                        : 
    'There has been an error, please contact our lovely administrator';
        die(
    $text);
    }

    if (
    mysql_num_rows($result) == 0) {
        die(
    'There is no data available');
    }

    // Print out the data
    echo '<table>';
    echo 
    '<thead>';
    echo 
    '<tr>';
    echo 
    '<th>Title</th>';
    echo 
    '<th>Studio</th>';
    echo 
    '<th>Year</th>';
    echo 
    '</tr>';
    echo 
    '<tbody>';
    while (
    $row mysql_fetch_assoc($result)) {
        echo 
    '<tr>';
        echo 
    '<td>' $row['title'] . '</td>';
        echo 
    '<td>' $row['studio'] . '</td>';
        echo 
    '<td>' $row['year'] . '</td>';
        echo 
    '</tr>';
    }
    echo 
    '</tbody>';
    echo 
    '</table>';

    // Free the data
    mysql_free_result($result);
    ?>

  11. #11
    SitePoint Evangelist
    Join Date
    Jan 2002
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Looks like it will do exactly what I want it to. I tried that code, and there was an error connecting to the database. I noticed the connection line lacked a variable, so I added it.

    I changed:

    PHP Code:
    ...
    $conn mysql_connect($db_host$db_user$db_pwd); 
    ... 
    To:

    PHP Code:
    ...
    $conn mysql_connect($db_host$db_name$db_user$db_pwd); 
    ... 
    Warning: mysql_connect(): Access denied for user: 'joshuasp_dvdlist@localhost' (Using password: YES) in /home/joshuasp/public_html/widescreenadvocate/watchdog.php on line 12
    Unable to connect to DB host: Access denied for user: 'joshuasp_dvdlist@localhost' (Using password: YES)

  12. #12
    PHP manual bot bronze trophy Gaheris's Avatar
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, my original code line was correct, see mysql_connect.
    But I did have a error in there use use this
    PHP Code:
    $conn mysql_connect($db_host$db_user$db_pwd);
    if (!
    $conn) {
        
    $text = (DEBUG) ? 'Unable to connect to DB host: ' mysql_error()
                        : 
    'There has been an error, please contact our lovely administrator';
        die(
    $text);

    instead of the original connection part.

  13. #13
    SitePoint Evangelist
    Join Date
    Jan 2002
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First of all, I would like to thank you so much for all your help so far. Secondly, there is still a parse error on line 12.

  14. #14
    PHP manual bot bronze trophy Gaheris's Avatar
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Did you run the following code?
    PHP Code:
    <?php
    // Connection information
    $db_host 'localhost';
    $db_user 'joshuasp_dvdlist';
    $db_pwd  'password';
    $db_name 'joshuasp_dvdlist';

    // Settings
    define('DEBUG'true);

    // Connecting
    $conn mysql_connect($db_host$db_user$db_pwd); 
    if (!
    $conn) {
        
    $text = (DEBUG) ? 'Unable to connect to DB host: ' mysql_error()
                        : 
    'There has been an error, please contact our lovely administrator';
        die(
    $text);
    }

    $result mysql_select_db($db_name);
    if (!
    $result) {
        
    $text = (DEBUG) ? 'Unable to select DB \'' $db_name ': \'' mysql_error()
                        : 
    'There has been an error, please contact our lovely administrator';
        die(
    $text);
    }

    // Getting the data
    $sql 'SELECT `title`, `studio`, `year` FROM `dvd`';
    $result mysql_query($sql$conn);
    if (!
    $result) {
        
    $text = (DEBUG) ? 'Could not successfully run query ('.$sql.'): ' mysql_error()
                        : 
    'There has been an error, please contact our lovely administrator';
        die(
    $text);
    }

    if (
    mysql_num_rows($result) == 0) {
        die(
    'There is no data available');
    }

    // Print out the data
    echo '<table>';
    echo 
    '<thead>';
    echo 
    '<tr>';
    echo 
    '<th>Title</th>';
    echo 
    '<th>Studio</th>';
    echo 
    '<th>Year</th>';
    echo 
    '</tr>';
    echo 
    '<tbody>';
    while (
    $row mysql_fetch_assoc($result)) {
        echo 
    '<tr>';
        echo 
    '<td>' $row['title'] . '</td>';
        echo 
    '<td>' $row['studio'] . '</td>';
        echo 
    '<td>' $row['year'] . '</td>';
        echo 
    '</tr>';
    }
    echo 
    '</tbody>';
    echo 
    '</table>';

    // Free the data
    mysql_free_result($result);
    ?>

  15. #15
    SitePoint Evangelist
    Join Date
    Jan 2002
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think my FTP program may have not been overwriting before. It works now. Now, the next thing to do is to get it to sort by each field. I would like people to be able to click on the table headings, which would be links, and have the list be sorted by that field.

    Glad to get the first part working!

  16. #16
    PHP manual bot bronze trophy Gaheris's Avatar
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this.
    PHP Code:
    <?php
    // Connection information
    $db_host 'localhost';
    $db_user 'joshuasp_dvdlist';
    $db_pwd  'password';
    $db_name 'joshuasp_dvdlist';

    // Settings
    define('DEBUG'true);
    $a_sortable = array('title''studio''year');
    $s_default  'title';

    // Preprocessing
    (isset($_GET['sort'])) ? (in_array($_GET['sort'])) ? $sortBy $_GET['sort']
                                                       : 
    $sortBy $s_default;
                           : 
    $sortBy $s_default;

    // Functions
    // This wonderfull function is property of Ichier (www.ichier.de)
    function array_csort() {
        
    $args func_get_args();
        
    $marray array_shift($args);

        
    $i 0;
        
    $msortline "return(array_multisort(";
        foreach (
    $args as $arg) {
            
    $i++;
            if (
    is_string($arg)) {
                foreach (
    $marray as $row) {
                    
    $sortarr[$i][] = $row[$arg];
                }
            } else {
                
    $sortarr[$i] = $arg;
            }
            
    $msortline .= "\$sortarr[".$i."],";
        }
        
    $msortline .= "\$marray));";

        eval(
    $msortline);
        return 
    $marray;
    }

    // Connecting
    $conn mysql_connect($db_host$db_user$db_pwd); 
    if (!
    $conn) {
        
    $text = (DEBUG) ? 'Unable to connect to DB host: ' mysql_error()
                        : 
    'There has been an error, please contact our lovely administrator';
        die(
    $text);
    }

    $result mysql_select_db($db_name);
    if (!
    $result) {
        
    $text = (DEBUG) ? 'Unable to select DB \'' $db_name ': \'' mysql_error()
                        : 
    'There has been an error, please contact our lovely administrator';
        die(
    $text);
    }

    // Getting the data
    $sql 'SELECT `title`, `studio`, `year` FROM `dvd`';
    $result mysql_query($sql$conn);
    if (!
    $result) {
        
    $text = (DEBUG) ? 'Could not successfully run query ($sql): ' mysql_error()
                        : 
    'There has been an error, please contact our lovely administrator';
        die(
    $text);
    }

    if (
    mysql_num_rows($result) == 0) {
        die(
    'There is no data available');
    }

    $resArray = array();
    while (
    $row mysql_fetch_assoc($result)) {
        
    $resArray[] = $row;
    }

    // Free the data
    mysql_free_result($result);

    // Sort the data
    if ($sortBy == 'year') {
        
    $resArray array_csort($resArray$sortBySORT_DESC);
    } else {
        
    $resArray array_csort($resArray$sortBy);
    }

    // Print out the data
    echo '<table>';
    echo 
    '<thead>';
    echo 
    '<tr>';
    echo 
    '<th>Title</th>';
    echo 
    '<th>Studio</th>';
    echo 
    '<th>Year</th>';
    echo 
    '</tr>';
    echo 
    '<tbody>';
    foreach (
    $resArray as $value) {
        echo 
    '<tr>';
        echo 
    '<td>' $value['title'] . '</td>';
        echo 
    '<td>' $value['studio'] . '</td>';
        echo 
    '<td>' $value['year'] . '</td>';
        echo 
    '</tr>';
    }
    echo 
    '</tbody>';
    echo 
    '</table>';
    ?>

  17. #17
    SitePoint Evangelist
    Join Date
    Jan 2002
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for being patient with me, and helping me through all this. Anyway, I'm not getting a parse error on line 15, with that new code.

  18. #18
    PHP manual bot bronze trophy Gaheris's Avatar
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    (isset($_GET['sort'])) ? (in_array($_GET['sort'], $a_sortable) ? $sortBy $_GET['sort']
                                                                   : 
    $sortBy $s_default)
                           : 
    $sortBy $s_default

  19. #19
    SitePoint Evangelist
    Join Date
    Jan 2002
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That fixed the error. Now the question is, how do I get the titles to link to the sort function?

  20. #20
    PHP manual bot bronze trophy Gaheris's Avatar
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I forgot that part, it was a bit lat last night.
    Anyway.
    PHP Code:
    echo '<th><a href="'.$_SERVER['PHP_SELF'].'?sort=title">Title</a></th>';
    echo 
    '<th><a href="'.$_SERVER['PHP_SELF'].'?sort=studio">Studio</a></th>';
    echo 
    '<th><a href="'.$_SERVER['PHP_SELF'].'?sort=year">Year</a></th>'
    There is one part I will probably build in later, is to change from ASC to DESC.

  21. #21
    SitePoint Evangelist
    Join Date
    Jan 2002
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem! This is looking really good. I would like to include a link to one of your sites in my web site credits, since you are building this yourself.

    Now that that is done, the only thing left is to make a form so that people can add a title to the database. I will also need to have an approval process where an admin will have to approve an entry before it is added. For studio, I want to have a drop down list of studio's, so people don't type Disney, and other's Walt Disney, as it would mess up the sorting.

    You can take a look at how it's coming here.

  22. #22
    PHP manual bot bronze trophy Gaheris's Avatar
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just quick update, it now handles ASC and DESC, just try it out.
    PHP Code:
    <?php
    // Connection information
    $db_host 'localhost';
    $db_user 'root';
    $db_pwd  '';
    $db_name 'joshuasp_dvdlist';

    // Settings
    define('DEBUG'true);
    $a_sortable     = array('title''studio''year');
    $s_sortDefault  'title';
    $a_orderable    = array('asc' => SORT_ASC'desc' => SORT_DESC);
    $s_orderDefault 'asc';

    // Preprocessing
    (isset($_GET['sort'])) ? (in_array($_GET['sort'], $a_sortable) ? $sortBy $_GET['sort']
                                                                   : 
    $sortBy $s_default)
                           : 
    $sortBy $s_sortDefault;
    (isset(
    $_GET['order'])) ? (isset($_GET['order'], $a_orderable) ? $orderBy  $_GET['order']
                                                                      : 
    $orderBy  $s_orderDefault)
                            : 
    $orderBy $s_orderDefault;

    // Functions
    // This wonderful function belongs Ichier (www.ichier.de)
    function array_csort() {
        
    $args func_get_args();
        
    $marray array_shift($args);

        
    $i 0;
        
    $msortline "return(array_multisort(";
        foreach (
    $args as $arg) {
            
    $i++;
            if (
    is_string($arg)) {
                foreach (
    $marray as $row) {
                    
    $sortarr[$i][] = $row[$arg];
                }
            } else {
                
    $sortarr[$i] = $arg;
            }
            
    $msortline .= "\$sortarr[".$i."],";
        }
        
    $msortline .= "\$marray));";

        eval(
    $msortline);
        return 
    $marray;
    }

    // Connecting
    $conn mysql_connect($db_host$db_user$db_pwd); 
    if (!
    $conn) {
        
    $text = (DEBUG) ? 'Unable to connect to DB host: ' mysql_error()
                        : 
    'There has been an error, please contact our lovely administrator';
        die(
    $text);
    }

    $result mysql_select_db($db_name);
    if (!
    $result) {
        
    $text = (DEBUG) ? 'Unable to select DB \'' $db_name ': \'' mysql_error()
                        : 
    'There has been an error, please contact our lovely administrator';
        die(
    $text);
    }

    // Getting the data
    $sql 'SELECT `title`, `studio`, `year` FROM `dvd`';
    $result mysql_query($sql$conn);
    if (!
    $result) {
        
    $text = (DEBUG) ? 'Could not successfully run query ($sql): ' mysql_error()
                        : 
    'There has been an error, please contact our lovely administrator';
        die(
    $text);
    }

    if (
    mysql_num_rows($result) == 0) {
        die(
    'There is no data available');
    }

    $resArray = array();
    while (
    $row mysql_fetch_assoc($result)) {
        
    $resArray[] = $row;
    }

    // Free the data
    mysql_free_result($result);

    // Sort the data
    $orderDiff = ($orderBy == 'asc') ? 'desc' 'asc';
    $resArray  array_csort($resArray$sortBy$a_orderable[$orderBy]);

    // Print out the data
    echo '<table>';
    echo 
    '<thead>';
    echo 
    '<tr>';
    echo 
    '<th><a href="'.$_SERVER['PHP_SELF'].'?sort=title&order='.$orderDiff.'">Title</a></th>';
    echo 
    '<th><a href="'.$_SERVER['PHP_SELF'].'?sort=studio&order='.$orderDiff.'">Studio</a></th>';
    echo 
    '<th><a href="'.$_SERVER['PHP_SELF'].'?sort=year&order='.$orderDiff.'">Year</a></th>';   
    echo 
    '</tr>';
    echo 
    '<tbody>';
    foreach (
    $resArray as $value) {
        echo 
    '<tr>';
        echo 
    '<td>' $value['title'] . '</td>';
        echo 
    '<td>' $value['studio'] . '</td>';
        echo 
    '<td>' $value['year'] . '</td>';
        echo 
    '</tr>';
    }
    echo 
    '</tbody>';
    echo 
    '</table>';
    ?>

  23. #23
    SitePoint Evangelist
    Join Date
    Jan 2002
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't quite know why, but now I'm getting this.

    Warning: mysql_connect(): Access denied for user: 'root@localhost' (Using password: YES) in /home/joshuasp/public_html/watchdog.php on line 49
    Unable to connect to DB host: Access denied for user: 'root@localhost' (Using password: YES)

    ALSO: I would like a function to count the number of records.
    Last edited by Joshua Clinard; Nov 4, 2003 at 13:22.

  24. #24
    PHP manual bot bronze trophy Gaheris's Avatar
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Uh, I changed the login data to test it on my local apache.

  25. #25
    SitePoint Evangelist
    Join Date
    Jan 2002
    Posts
    457
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great. Can you make it so that the First time you click the link it is displayed ascending, and the second time it is descending? (I might have this backwards, but what I want is the oppisite of what is happening now.) Great work so far though. I also need a feature to count the records, and display them somewhere, if you didn't see it in my previous post. [img]images/smilies/wink.gif[/img]

    I took a look at the php manual, and I think mysql_num_rows is the function to use. I'll try to play around with it some when I get a chance.
    Last edited by Joshua Clinard; Nov 4, 2003 at 17:02.


Bookmarks

Posting Permissions

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