...

View Full Version : Stuck in database development



Ctechinfo
02-17-2013, 08:25 AM
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:

<!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..

Ctechinfo
02-17-2013, 08:54 PM
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.. :D

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


<!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>

Old Pedant
02-17-2013, 09:27 PM
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:


$query = "SELECT * FROM allBoards WHERE folder = 'asus/pegatron' ORDER BY codename ASC";

Or, probably much better for your purposes:


$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.

Ctechinfo
02-17-2013, 10:53 PM
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

Old Pedant
02-18-2013, 12:02 AM
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:


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:

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.

Ctechinfo
02-18-2013, 01:00 AM
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:


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:

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...

Ctechinfo
02-18-2013, 01:48 AM
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

Ctechinfo
02-18-2013, 03:04 AM
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..

Old Pedant
02-18-2013, 04:29 PM
You missed the spacing:

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.


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.

Ctechinfo
02-18-2013, 08:13 PM
You missed the spacing:

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.


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.

Ctechinfo
02-27-2013, 01:32 PM
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..:o

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.

Old Pedant
03-01-2013, 12:25 AM
AAARRGGHH!!

Missing the *REQUIRED* parentheses! I looked at that a half dozen times and missed it!


CREATE TABLE codeNames (
boardID INT,
codeName VARCHAR(100),
CONSTRAINT FOREIGN KEY ( boardID ) REFERENCES allBoards(boardID)
) ENGINE INNODB;

Ctechinfo
06-04-2013, 12:51 PM
AAARRGGHH!!

Missing the *REQUIRED* parentheses! I looked at that a half dozen times and missed it!


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..

FishMonger
06-08-2013, 09:14 PM
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 (http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/intro-to-normalization.html)

Also, you may find it easier to design if you use a development tool such as Mysql Workbench (http://dev.mysql.com/downloads/tools/workbench/). It's free.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum