mySQL FASTER Querys!

[MasterT]

im a noobie to php and mySQL. i got to make querys for a page like this (http://www.mxtracks.us/mx/California/Hollister/hollisterhills.html) but it takes so long when i load the page. i think its cause i'm selecting the same row over and over like this


<?PHP
//connect to database
$dbh=mysql_connect ("localhost", "user name", "password") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("database name");
?>

here's the problem
im wondering if there's a faster way to do this.

<?PHP
$selection = mysql_query("SELECT * FROM tracks WHERE id='1'");
while ( $row = mysql_fetch_array($selection) ) {
echo ($row["name"]);
} ?>
i have to put this for each query but i only need the one row for each page is there a way to say at the top of the page . hey i need this row and then when you need something in the row you don't have to say hey i need this same row again.

if i can't do that what can i do to speed it up?

[HardCoded]

Hi,

First, make sure that field id is indexed. If it is,then you should go

mysql_query("SELECT * FROM tracks WHERE id=1");
Next, you don't need to enter a while loop to get one row. Just go,

$row = mysql_fetch_array($selection);

Next, the way you are calling fetch_array actually returns two arrays. Since you just want to reference the array by field name, do this:

$row = mysql_fetch_array($selection, MYSQL_ASSOC);

And finally, once you have something stored in $row, you can access it as many times as you want on your page.

<?
$row = mysql_fetch_array($selection, MYSQL_ASSOC);
echo $row['name']
?>
Some html...
<?=$row['artist'] ?>
Some more html
<?=$row['genre'] ?>
And so on

[MasterT]

thanks so much HardCoded your my hero

here's some other things you may want to look at
my test page
http://mxtracks.us/mx/test/test2/adrenalin.php

mySQL layout
http://mxtracks.us/Temp/database.png

my Fields
http://mxtracks.us/Temp/fields.png

my Indexes
http://mxtracks.us/Temp/indexes.png

[MasterT]

is it good or bad to have html code in the database?

[steve0]

As your data grows, the benefits of indexes will become more beneficial. If your tables are relatively small.. the benefits are not as appearent (due to overhead).

You may also want to do a little research on "table structures", "data normalization", "field types" and "JOIN".

It looks like you are using one table with lots of varchar fields..

Since computers prefer numbers over strings.. you should consider multiple tables for your attributes.. e.g. track_type, dirt_type and use the id as the identifier.

[MasterT]

I updated these
my Fields
http://mxtracks.us/Temp/fields.png

my Indexes
http://mxtracks.us/Temp/indexes.png

steve0 i have some questions.
why would i put my data into multiple tables?

how would that be layed out?


P.S. I HATE research

[mushroom]

Fixed length data bases search faster than varable lenght ones, also duplcate data should be avoided IE cities.

Create a new table called cities with fields called id (smallint) and city (varchr(50). Alter your main table city to smallint and place the id (new table) of city in this field. etc.

Move all your varable length field to new tables if possible.

Many varable lenght tables scearch faster than one big varable lenght table. And fixed lenght tables search even faster.

[MasterT]

wow mushroom its all clear now

the varchar ones are fixed im guessing, so i could leave those in their own table

then i could make a table for the cities, although there wont be many duplicates (5/56), but what your saying is assign an ID to a city in the City table then in the main table i will place the assigned id in the City field for the row its needed
but how will it know to go to the city table to get the city name?

then i can make another table call it attributes or something like that and then put all of the text, longtext, and tinytext into that table?

is there an easier way to move my data other than deleting it and re-inputing it?
cause i have 22 rows already :(

[MasterT]

Ooo i forgot to show you guys. I already have this data in static HTML Pages

http://mxtracks.us/mx/California/index.html

[mushroom]

is there an easier way to move my data other than deleting it and re-inputing it?
cause i have 22 rows already :(

You could write a php script to do it for you but that is not worth the effort with only 22 rows. My main table has 530+ fields and 50,000+ rows with 20+ support tables.

If you do not think you will exceed 100 rows stick with what you have. If you are thinking big do it now.



but how will it know to go to the city table to get the city name?:(

With php/mysql comb you will have query to get the id (city) then query to the actual value. With small data bases it a nusence but with large data bases it's the norm in order to maitain speed.

[MasterT]

ok thank you mushroom you are a big help


50,000+ rows
what kinda database is that?

[MasterT]

i finished inputing all my data the table comes to a grand total of 118.9KB. now all i need to do is learn PHP

[mushroom]

ok thank you mushroom you are a big help


50,000+ rows
what kinda database is that?

A categorized image location db.

And in order to speed up writing start useing multiple smaller sripts placeing things you use all the time in their own sript IE; dbconect.php
<?PHP
//connect to database
$dbh=mysql_connect ("localhost", "user name", "password") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("database name");
?>
Then any time you need it you type

include ("dbconect.php");

into your other srcipts and for things you use more than once on a single script exicution look into making a functions.php
a starthtml.php and endhtml.php can be usefull.

[MasterT]

people were telling me that before but i was confused as to why i would do that
Thanks again mushroom you make everything so clear