Calling Information from the database

[conor]

hi,

I need to call just one column from a database with MySQL/PHP. I don't want to state how many rows I want so that it will take all of the rows and I can add more rows without changing this code.

The more tricky part of this is that i need to wrap tags around these rows. For Example i have a column with two rows called page1 and page2. I want to call the whole column from the database so that links to page1 and page2 will appear. But in the database it will just say page1 and page2, therefore i need to wrap link tags around the names.

The most importand thing is i cannot state how many rows i want, so that in the future if i add a page3 it will automatically be displayed on the page without me needing to add more to the PHP code. Also when i add a page3 i will want it to be a link aswell without me having to change anything. I'm probably getting into rough teritory here, my PHP isn't brilliant and i would apprechiate some help!

cheers

 

[jnjc]

There are many different ways to do this. Basically you are looking at using a "while loop" on your table to extract each row. The exact code for this will depend on what PHP/MySQL you are running, but in a basic form it should look something like this:

$sql = "SELECT id as userid, fullname, userstatus 
        FROM   sometable
        WHERE  userstatus = 1";

$result = mysql_query($sql);


while ($row = mysql_fetch_assoc($result)) {
   // This section of code will execute for each row in the table
    echo $row["userid"];
    echo $row["fullname"];
    echo $row["userstatus"];
}

mysql_free_result($result);

As I said depending on version there are more advanced ways of doing this using PDO, MySQLi etc. but the basic concept is the same...

HTH,
JC

 

[conor]

thats not exactly what i mean... i'm finding this very hard to explain!

Ill show you the table:

+----------+
| pagename |
+----------+
| Home     |
| link_1   |
+----------+

i want the links to display like this:

home | link_1

But i cant get them directly by id because if i add a link_2 to the table then i want it to just appear on the page as a link.

Any ideas?

 

[jnjc]

Are you trying to have a table that will store your links for each page/ whole site ?

If that is the case then you should really have multiply records, one for each link.

So the table would look something like:

Fields:
ParentPage
Order
URL
LinkDesc


Then use the code I posted earlier to scan each record in the table and do something like:

while ($row = mysql_fetch_assoc($result)) {
   echo "<a href={$row["URL"]} />{LinkDesc}</a>";
}

Then as you want to add a new link to the page just add a new record....

 

[conor]

i will do something like that in the future but for the moment i want to get everything working at a basic level so im doing this:

while ($row = mysql_fetch_assoc($result)) {
   echo "<a href=\"http://url.com/{$row["pagename"]}.php\">{$row["pagename"]}</a>";
}

how do i get it to echo a second and third url from the same table - just down a row?

 

[jnjc]

how do i get it to echo a second and third url from the same table - just down a row?

I think we may be getting our wires crossed on terminology

When I refer to a row I am referring to a record. So you can have one table with 10 fields and 100 rows (records). If this is what you mean by row then the example I have provided will do what you want.

I suspect that you are trying to have one table, one record with 10 fields, each field a link. If that is the way you are trying to do things (I really don't recommend it), then your best bet is to do a simple loop through the $row variable and make a link from each field in it? Us something like the
array_keys - function to get a list of fields in the $row array and using it do a loop to build your links.

HTH,
JC

 

[conor]

right thanks. i think thats what i mean, ill do a bit of research on the array_keys. Why do you not recommend using it ?

 

[jnjc]

Why do you not recommend using it ?

It's not the correct way to use a database. The point of a database is that you can have multiply "records" within one table. As you change things you add/remove records. You are trying to do the same thing but only using one one record and as you change things adding/removing fields. The disadvantages are (and I won't even get started on DB normalisation):

1) Limited functionality. At the moment you are looking at using the field name as your link text and the actual value as the URL. Say you want to add a title to the link ? By having a record for each link you just add a new field to the table and you can have a title per link ....
2) How will your links be ordered (again see above)
3) Deployment. Say you want to reuse this DB, your DB structure will be different for each site.
4) Scalability - What do you do if you want a different menu structure on different pages ?
5) Link Text, you are restricted on what you can have as a field name, including the fact you can't have spaces in there, so if you want to have a link called "Go To Cart", your method won't work (you may not need spaces now but some day....)

All in all in my opinion you are not only limiting yourself doing it this way but you are also increasing your work load by having to use functions to interrogate the $row array etc.

 

[conor]

rite i get you! thanks for explaining!