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 14 of 14
  1. #1
    Regular Coder
    Join Date
    Sep 2012
    Posts
    100
    Thanks
    5
    Thanked 3 Times in 3 Posts

    Stuck in database development

    I am looking to setup a database for my "test server"/offline access to collect data as I find it so it will be easier to compile the data to put the info online in a more accurate fasion.

    I have a basic jist of how to do the database, but I do have a few questions.

    Qustion 1) I know for displaying row information you use $row['row_name'], would $table['table_name'] work the same way? Essentially what I am tryinging to do is build the database so that I can output the information in a tabular format...

    For instance:
    Code:
    <!doctype html public "-//W3C//DTD HTML 4.01 transitional//EN">
    <html>
    	<head>
     		<title>Ctechinfo - Motherboard Database</title>
    		<meta name="description" content='Ctechinfo - Your unofficial #1 Compaq/HP resource website. The largest database of Compaq/HP motherboard information on the internet.'/>
            <meta http-equiv="content-type" content="text/html; charset="utf-8">
            <meta name="keywords" content='Compaq, HP, Hewlett-Packard, motherboard information, Asus, BCM/GVC, BIOSTAR, Chaintech, ECS, Elite Group, FIC, First International Computer, Foxconn, Gigabyte, Intel, LiteOn, Mitac, MSI, Microstar International, Privas, Quanta, Tatung, Tekram, TPV-Inventa, Trigem'>
            <meta name="author" content="Toby Bibeau"> 
            <meta name="robots" content="noindex,nofollow">
    </head>
    
    <body bgcolor="#ffffff" margin="5px" font-size="14px" font-family="verdana">
    
    <?php
    		//Connect to the server and select database; you may need it
    		$dbc = mysqli_connect('localhost', 'root', '5GH9A4X1', 'boards')
    		  or die("Could not connect to the database.");
    			
    	$query = "SELECT * FROM `asus` ORDER BY `real_name` ASC LIMIT 0, 30 ";
    	$data = mysqli_query($dbc, $query);
    	
    
    while ($row = mysqli_fetch_array($data)) {
    	//Show results table
    	print '<table><tr><th colspan='."6".'><center>'.$table['asus'].'</center></th></tr>
    		<tr><td>' . $row['real_name'] . '</td><td>'.$row['ver'].'</td><td>'.$row['code_name'].'</td></tr>'.$row['part_number'].'</td></tr>'.$row['spare'].'</td><td>'.$row['colors'].'</td></tr></table>';
    }
    mysqli_close($dbc);
    ?>
    
    </body>
    </html>
    The table name is fluid I have 16 folders to work from and want the code to self adjust (for the table name) per folder (1 html table per folder). The Row Names will but output'ted code via a normal echo, but the row data will be set. btw the above produces a blank page.. and php.ini has "errors on"

    Question 2) When entering data. How could I mesh the data together for instance on one day I find a code name and a spare part number for a particular board, then two weeks later I find a real name or a retail part number.

    Would also like to build an html data entry page instead of using the phpMyAdmin interface..

  • #2
    Regular Coder
    Join Date
    Sep 2012
    Posts
    100
    Thanks
    5
    Thanked 3 Times in 3 Posts
    ok so after a lot of tweaking I got the code redone and it works to the point the the table row data is displayed. I hard coded the table header for now til I figure out how to display table names like the rows..

    I had a little trouble with the sorting, was using DESC, but determined ASC was what I wanted for my purpose..

    Not I need to figure out how to fill the voids (blank spots in the html table based off the database..

    PHP Code:
    <!doctype html public "-//W3C//DTD HTML 4.01 transitional//EN">
    <html>
        <head>
             <title>Ctechinfo - Motherboard Database</title>
            <meta name="description" content='Ctechinfo - Your unofficial #1 Compaq/HP resource website. The largest database of Compaq/HP motherboard information on the internet.'/>
            <meta http-equiv="content-type" content="text/html; charset="utf-8">
            <meta name="keywords" content='Compaq, HP, Hewlett-Packard, motherboard information, Asus, BCM/GVC, BIOSTAR, Chaintech, ECS, Elite Group, FIC, First International Computer, Foxconn, Gigabyte, Intel, LiteOn, Mitac, MSI, Microstar International, Privas, Quanta, Tatung, Tekram, TPV-Inventa, Trigem'>
            <meta name="author" content="Toby Bibeau"> 
            <meta name="robots" content="noindex,nofollow">
    </head>

    <body bgcolor="#ffffff" margin="5px">

    <table width="98%" border="1" cellpadding="5"><tr><th bgcolor="#c2c2c2" colspan="6">Asus/Pegatron</th></tr>
    <tr><td><center>Real Name</center></td><td><center>Version/Revision</center></td><td><center>Codname</td><td><center>Part Number</center></td><td><center>Board Colors</center></td><td><center>Notes</center></td></tr>
    <?php
            
    //Connect to the server and select database; you may need it
            
    $dbc mysqli_connect('localhost''root''5GH9A4X1''boards')
              or die(
    "Could not connect to the database.");
                
        
    $query "SELECT * FROM `asus/pegatron` ORDER BY `codename` ASC";
        
    $data mysqli_query($dbc$query);
        

    while (
    $row mysqli_fetch_array($data)) {
        
    //Show results table
        
    echo '<tr><td>' $row['realname'].'</td><td>'.$row['ver'].'</td><td>'.$row['codename'].'</td></tr>'.$row['partnumber'].'</td><td>'.$row['colors'].'</td><td>'.$row['notes'].'</td></tr>';
    }
    mysqli_close($dbc);
    ?>
    </table>
    </body>
    </html>

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,640 Times in 4,602 Posts
    But now the problem is that you have a very bad database design.

    There is NO REASON to have one database table per "folder" (whatever a folder means to you) and many, many reasons to only have a single table for *ALL* folders.

    So if what you are talking about is that one of your "folders" corresponds to "asus/pegatron", then you should put *ALL* your "folders" into one table and add a column named "folder".

    And then you would do, instead of what you show:
    Code:
    $query = "SELECT * FROM allBoards WHERE folder = 'asus/pegatron' ORDER BY codename ASC";
    Or, probably much better for your purposes:
    Code:
    $query = "SELECT * FROM allBoards WHERE folder = '$foldername' ORDER BY codename ASC";
    And now you can use one PHP page to show info for any "folder".

    Don't start from a bad database design and then hope to compensate for it in your PHP code. Get the DB design right first and then the PHP will be simpler.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Ctechinfo (02-17-2013)

  • #4
    Regular Coder
    Join Date
    Sep 2012
    Posts
    100
    Thanks
    5
    Thanked 3 Times in 3 Posts
    Thanks for the reply.

    The website this database that I am collecting information for currently contains 16 folders one for each OEM manufacturer (15 known and a folder for unknown/OEM[i.e. unbranded unique design]). with pages for each motherboard in the appropriate folder.

    The project is a huge one that has moved into its 13th year and I have come to the conclusion that its time to pool the data collected into a db versus having a bazillion different places to look while building the web pages. Mainly to help aleviate errors in the data.

    The original concept of the project was based on desktop boards only but as time wears on we are uncovering retail manufactures for laptop and Workstation/Server boards

    So to answer your inquiry, the current setup the tables correspond to the manfuactures:
    Asus/Pegatron
    BCM/GVC
    BIOSTAR
    Chaintech
    ECS
    FIC
    Foxconn
    Gigabyte
    Intel
    LiteOn
    Mitac/Tyan
    MSI
    OEM/Unknown
    <- dump folder for either boards with only tidbits of info or strict custom design by an unknown manufacturer

    Privas
    Quanta
    Tatung
    Tekram
    TPV-Inventa
    Trigem



    Current "design" only have one table "asus/pegatron" so far...

    And the ultimate goal is to be able to pool the data together to eventually wind up with this example

    Asus A7N8X-LA
    Known Codenames: Escape, Explorer, Explorer2, Explorer4 Focus
    Processor Info: AMD Athlon XP3400+ (Socket/FSB info as well)
    RAM Info: Number/Type of Slots/Sockets, FSB, etc
    Chipset Info: Brand/Series info (North and South Brige info)
    Type of video options: (i.e Integrated XXXX HD Graphics and PCI x16 Graphics cards)
    Type of Audio options: (i.e. Integrated XXXX HD Audio with CODEC: xxxxx)
    Expansion Slots: (i.e 2x PCI Express x16 Graphics Slots/4x PCI Express x1 Slots)
    Known Spare Part #'s: (essentially the part numbers used to order replacements from the Compaq/HP)
    Documentation Availible: (manual, Quick Reference Guide, Maintenance/Service Guide)
    Models Found in: (broken down by branding [i.e. Compaq, Compaq/HP, HP]
    Confirmed reflash: (Some people have been able to reflash their BIOS back to the retail version)

    And with better collected info I can update/build pages with more accuracy.

    Website/page disscusion that coincides with this project.
    http://www.codingforums.com/showthread.php?t=273529
    Last edited by Ctechinfo; 02-18-2013 at 12:51 AM. Reason: Added link to other thread for more information

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,640 Times in 4,602 Posts
    So, again. One table. "allBoards" seems like a good name, but up to you.

    And add one field, "manufacturer", which is where you distinguish the board maker.

    But now comes the much harder part: It is considered very bad DB design to put a *LIST* of values into a single field. So, just to pick one example, you should not do this:
    Code:
    CREATE TABLE allBoards (
        manufacturer VARCHAR(50),
        trueName VARCHAR(100),
        knownCodenames VARCHAR(1000),
        ... other fields ...
    );
    
    INSERT INTO allBoards (manufacturer, trueName, knownCodenames)
    VALUES( 'Asus/Pegatron', 'Asus A7N8X-LA', 'Escape, Explorer, Explorer2, Explorer4 Focus');
    Instead, you *SHOULD* do:
    Code:
    CREATE TABLE allBoards (
        boardID INT AUTO_INCREMENT PRIMARY KEY,
        manufacturer VARCHAR(50),
        trueName VARCHAR(100),
        ... other fields ...
    ) ENGINE INNODB;
    
    CREATE TABLE codeNames (
        boardID INT,
        codeName VARCHAR(100),
        CONSTRAINT FOREIGN KEY boardID REFERENCES allBoards(boardID)
    ) ENGINE INNODB;
    
    INSERT INTO allBoards (boardid, manufacturer, trueName)
    VALUES( 331, 'Asus/Pegatron', 'Asus A7N8X-LA');
    
    // this creates 4 records in the codeNames table:
    INSERT INTO codeNames (boardid, codename)
    VALUES( 331, 'Escape'), (331, 'Explorer'), (331, 'Explorer2'), (331, 'Explorer4 Focus');
    [Normally, you would not assign the boardid in the INSERT INTO allBoards; you would let MySQL assign it for you. But I did it here for demo purposes.)

    Do you HAVE to do this? No. But depending on what queries you may later make against the DB, it may prove invaluable. Each field you have there that potentially has a list of values in it needs to be examined to figure out if it should be done as above (known as a "1 to many relationship" or, sometimes, a "lookup table").

    Even the fields that only hold a single value might need this treatment. The most likely candidate, as I see it (but you would need to carefully review your data to know for sure) is "CPUInfo". But there well could be others.

    A *good* DB design is one that will allow you to later make most any query against it that you can dream up. Just one possible example:
    "Find all boards that support an AMD Athlon XP3400+ CPU and that have at least 4
    RAM slots and at least 3 PCI Express x1 Slots."

    You won't easily be able to support such a query if you have multiple pieces of information in a single DB field.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Regular Coder
    Join Date
    Sep 2012
    Posts
    100
    Thanks
    5
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by Old Pedant View Post
    So, again. One table. "allBoards" seems like a good name, but up to you.

    And add one field, "manufacturer", which is where you distinguish the board maker.

    But now comes the much harder part: It is considered very bad DB design to put a *LIST* of values into a single field. So, just to pick one example, you should not do this:
    Code:
    CREATE TABLE allBoards (
        manufacturer VARCHAR(50),
        trueName VARCHAR(100),
        knownCodenames VARCHAR(1000),
        ... other fields ...
    );
    
    INSERT INTO allBoards (manufacturer, trueName, knownCodenames)
    VALUES( 'Asus/Pegatron', 'Asus A7N8X-LA', 'Escape, Explorer, Explorer2, Explorer4 Focus');
    Instead, you *SHOULD* do:
    Code:
    CREATE TABLE allBoards (
        boardID INT AUTO_INCREMENT PRIMARY KEY,
        manufacturer VARCHAR(50),
        trueName VARCHAR(100),
        ... other fields ...
    ) ENGINE INNODB;
    
    CREATE TABLE codeNames (
        boardID INT,
        codeName VARCHAR(100),
        CONSTRAINT FOREIGN KEY boardID REFERENCES allBoards(boardID)
    ) ENGINE INNODB;
    
    INSERT INTO allBoards (boardid, manufacturer, trueName)
    VALUES( 331, 'Asus/Pegatron', 'Asus A7N8X-LA');
    
    // this creates 4 records in the codeNames table:
    INSERT INTO codeNames (boardid, codename)
    VALUES( 331, 'Escape'), (331, 'Explorer'), (331, 'Explorer2'), (331, 'Explorer4 Focus');
    [Normally, you would not assign the boardid in the INSERT INTO allBoards; you would let MySQL assign it for you. But I did it here for demo purposes.)

    Do you HAVE to do this? No. But depending on what queries you may later make against the DB, it may prove invaluable. Each field you have there that potentially has a list of values in it needs to be examined to figure out if it should be done as above (known as a "1 to many relationship" or, sometimes, a "lookup table").

    Even the fields that only hold a single value might need this treatment. The most likely candidate, as I see it (but you would need to carefully review your data to know for sure) is "CPUInfo". But there well could be others.

    A *good* DB design is one that will allow you to later make most any query against it that you can dream up. Just one possible example:
    "Find all boards that support an AMD Athlon XP3400+ CPU and that have at least 4
    RAM slots and at least 3 PCI Express x1 Slots."

    You won't easily be able to support such a query if you have multiple pieces of information in a single DB field.
    Fortunately you get the jist of what I am trying to accomplish. The final design of the database is coming together using insight from this..

    Gonna work on getting things setup, I may be back with more questions but the insight has at least steered my in the right direction...

  • #7
    Regular Coder
    Join Date
    Sep 2012
    Posts
    100
    Thanks
    5
    Thanked 3 Times in 3 Posts
    Error

    SQL query:

    CREATE TABLE codeNames(

    boardID INT,
    codeName VARCHAR( 100) ,
    CONSTRAINTFOREIGNKEY boardID REFERENCES allBoards( boardID )
    ) ENGINE INNODB;



    MySQL said:
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REFERENCES allBoards(boardID)
    ) ENGINE INNODB' at line 4
    Last edited by Ctechinfo; 02-18-2013 at 02:59 AM.

  • #8
    Regular Coder
    Join Date
    Sep 2012
    Posts
    100
    Thanks
    5
    Thanked 3 Times in 3 Posts
    So the premise of Auto Increment is the database takes care of numbering, if that is the case why am I being forced to enter a number per entry? shouldn't the first entry start at 1, and each entry after go from there?

    Trying to pupulate the allBoards table with info.. And coincidentally even though I created it as allBoards its showin as allboards..

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,640 Times in 4,602 Posts
    You missed the spacing:
    Code:
        CONSTRAINT   FOREIGN  KEY  boardID REFERENCES allBoards( boardID )
    **********

    You must either omit the field name of the auto_increment field from your INSERT *or* supply a NULL as the value, else the value you give overrides the auto_increment feature.
    Code:
    INSERT INTO allBoards( boardID, manufacturer, ...) VALUES( NULL, 'Asus', ... )
    *OR*
    INSERT INTO allBoards( manufacturer, ...) VALUES( 'Asus', ... )
    **************

    If you are on Windows, the case (upper vs. lower) of table names is not significant and by default all are converted to lower case by MySQL. I believe that even on Linux this behavior is optional.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Regular Coder
    Join Date
    Sep 2012
    Posts
    100
    Thanks
    5
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by Old Pedant View Post
    You missed the spacing:
    Code:
        CONSTRAINT   FOREIGN  KEY  boardID REFERENCES allBoards( boardID )
    **********

    You must either omit the field name of the auto_increment field from your INSERT *or* supply a NULL as the value, else the value you give overrides the auto_increment feature.
    Code:
    INSERT INTO allBoards( boardID, manufacturer, ...) VALUES( NULL, 'Asus', ... )
    *OR*
    INSERT INTO allBoards( manufacturer, ...) VALUES( 'Asus', ... )
    **************

    If you are on Windows, the case (upper vs. lower) of table names is not significant and by default all are converted to lower case by MySQL. I believe that even on Linux this behavior is optional.
    The error posted did have spacing where the OP had them, lost in copy-paste.. I fixed the CREATE TABLE but missed the rest.. thanks for the info.
    Last edited by Ctechinfo; 02-18-2013 at 09:21 PM.

  • #11
    Regular Coder
    Join Date
    Sep 2012
    Posts
    100
    Thanks
    5
    Thanked 3 Times in 3 Posts
    First off, Old Pendant, sorry about not recognizing you in my last post. Reviewing the thread this morning I realized you were the only one to reply..

    I am still toying with the idea of this project but haven't really had the time to devote to it. Just recently rejoined the workforce.

    As far as what has been discussed so far.

    When I tried creating the:
    CREATE TABLE codeNames (
    boardID INT,
    codeName VARCHAR(100),
    CONSTRAINT FOREIGN KEY boardID REFERENCES allBoards(boardID)
    ) ENGINE INNODB;

    I am not sure if it is the version of MySQL or not that is causing the hangup, but having the constraint line in the create block as you have it, it produced the error posted above.. The formatting went loopy from mySQL to here, but it was entered as you wrote it.

    I did manage to pick up a couple more books covering php/mysql so hopefully I can finally grasp things better.

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,640 Times in 4,602 Posts
    AAARRGGHH!!

    Missing the *REQUIRED* parentheses! I looked at that a half dozen times and missed it!
    Code:
    CREATE TABLE codeNames (
        boardID INT,
        codeName VARCHAR(100),
        CONSTRAINT FOREIGN KEY ( boardID ) REFERENCES allBoards(boardID)
    ) ENGINE INNODB;
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Ctechinfo (03-01-2013)

  • #13
    Regular Coder
    Join Date
    Sep 2012
    Posts
    100
    Thanks
    5
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by Old Pedant View Post
    AAARRGGHH!!

    Missing the *REQUIRED* parentheses! I looked at that a half dozen times and missed it!
    Code:
    CREATE TABLE codeNames (
        boardID INT,
        codeName VARCHAR(100),
        CONSTRAINT FOREIGN KEY ( boardID ) REFERENCES allBoards(boardID)
    ) ENGINE INNODB;
    I haven't gotten around to trying it out yet, but I am going to sometime soon. Haven't had the spare time to put into the project, but definitely need to get going on it... Figures just about the time I get some free time, I break my glasses..

  • #14
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,944
    Thanks
    2
    Thanked 170 Times in 165 Posts
    Before you get to far down the road it might be best for you to do some reading on database normalization. That will give you a great deal of help in designing your database.

    An Introduction to Database Normalization

    Also, you may find it easier to design if you use a development tool such as Mysql Workbench. It's free.


  •  

    Posting Permissions

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