Call Data From Mysql ...

[TRshady]

Hey all ... have been trying to for ages but simply cant get it....

all I want to do is have a mysql table like this:

section ---- title ---------------------------- content ------

guides ----- improve performance ----[long text]


and then be able to call it into pages ......
anyone able to give me a little tut to do this please? :(

[true_neo]

Well.
I got a teensy weensy bit of sql experience after pissing about with forums all the time, so heres something that MIGHT woik:

$dbh=mysql_connect ("[your host, usually localhost]", "[database username", "[database password]");
mysql_select_db ("[database name]");

After you made your table in the base with something like
mysql_query ("CREATE TABLE '[table name]' ('[rowname]' varchar([number of charas in your text]) DEFAULT'[default value]' NOT NULL) TYPE=MyISAM;");

To fetch the data:
mysql_query ("SELECT * FROM '[table name]' WHERE [info]='$var'");

this is from within a php script
for safety reasons, do not keep your passwords in such a file public :P


O and when you replace my [things] with your actual info, take the [] away as well.
any more questions/errors, then talk back and Ill do my best.
//out

[I.am]

phpmyadmin?

[CoolMac]

phpmyadmin es just a way to make mysql commands easyier to do

[TRshady]

I do have phpmyadmin .. and am able to use it. I just dont have a clue how people go about calling that data to pages and whats the best way to go about doing it ...

[Cl1mh4224rd]

Generic PHP code...

$link = mysql_connect('server', 'username', 'password');
mysql_select_db('database');

$result = mysql_query('SELECT * FROM table');
while ($row = mysql_fetch_assoc($result)) {
   echo 'Field 1: '.$row['field1'].'<br />'.
        'Field 2: '.$row['field2'].'<br />'.
        'Field 3: '.$row['field3'].'<br />';
}

[TRshady]

Seems pretty straight forward ... but even simple scripts can confuse me if I dont fully understand it ... care to give a brief explanation on how to use that script with a ready made mysql table please?

Cheers.

[Nogimics]

This isnt so hard and there are plently of tutorials on how to do this ill give you a quick guide and you can fire some questions back at us. However true_neo has got you started already so you should have replied to his post.

If phpmyadmin is installed then making your table is simple and im expecting you to have at least done that.

Easiest way to call the data and show it is to use a mixture of PHP and MySQL.

Firstly you have to connect to the database

<?php

&#036;db = mysql_connect("localhost", "username", "password");
mysql_select_db ("database name");

/* This is a comment tag and it wont show on your page. Now you need to fetch the data. Seing as I dont understand what im fetching because your description of your table is vague here is one way of fetching data and outputting it */

&#036;result = mysql_query("select * from table");
while(&#036;r=mysql_fetch_array(&#036;result)) {
&#036;section = &#036;r[section];
&#036;title = &#036;r[title];
&#036;content = &#036;r[content];

/* Presuming the 3 title&#39;s: Section, title and content were colum headings and you have rows of data underneath them then this would fetch every row of data under those headings. the next part will print the data to the page and stop the query */

echo &#036;section;
echo &#036;title;
echo &#036;content;

} // <--that character ending the query

?> <--and this closed PHP

All the variables in BOLD is data im assuming you know. What I have written above doesnt show off my great coding practice but it should work all the same.

When you save the document make sure you save it as filename.php so it gets parsed through a PHP parser.

[TRshady]

Thank you very much for the reply&#33; ....
Its so helpful and Ive actually managed to understand and follow it&#33; :D

So I used this code:


<?php

&#036;db = mysql_connect("localhost", "triley_test", "test");
mysql_select_db ("triley_content");

&#036;result = mysql_query("select * from software");
while(&#036;r=mysql_fetch_array(&#036;result)) {
&#036;section = &#036;r[section];
&#036;title = &#036;r[title];
&#036;content = &#036;r[content];

/* Presuming the 3 title&#39;s: Section, title and content were colum headings and you have rows of data underneath them then this would fetch every row of data under those headings. the next part will print the data to the page and stop the query */

&#036;txt="
";

echo &#036;section;
echo &#036;txt;
echo &#036;title;
echo &#036;txt;
echo &#036;content;

}

?>

and the result is this (http://rileysresource.net/testdb.php), which is exactly what I wanted. But now I have done this .. I have a few questions I hope can be answered.

1. How do I go about formatting?
- I assume is would be best to use css right?
- or would I add tags to the text in the db?

2. How would I use this?
- for example I have a link to a &#39;blubster review&#39;, would I need to create a page similar to the code above for it and link to that page? .. or is there a more efficient way of doing this. Maybe having one page which gets variables in the url and loads a page?

3. How can I make entered data keep current formatting?
- I placed a page of text, being a guide into the table, and when viewed appears right with all the seperate paragraphs, breaks etc .. but when called into a page .. appears as on block .. any way to corrent this?

I&#39;ll have more, but for now I&#39;d be really happy if these could be answered.
Thanks again for being so helpful&#33;

[I.am]

It would be best if you use css and a generic build.php. That build.php is your normal template of the whole website. So in your index.php load build.php and u can do the same in other pages too.

Your css file contains how the headers will look like and the various div&#39;s so to speak. Use print to display the values on the page.

So all you are doing in actual pages are calling build.php for the template, calling css for all the styles, and then calling text from database and printing them using the styles.

I know I am being more conceptual than giving you the actual code. But I am sure you can do it... :)

Edit: oh by the way you dont need to have all the pages as this. You can include a function in build.php that gets the docname from the database. Also, use DIV&#39;s a lot. Will expand on this later, gotta go & catch on some sleep :D

[TRshady]

thanks for the reply ....
I get what you mean ... well, most of it.
I do similar at the moment with my site, with each page looking like this (http://www.rileysresource.net/software/reviews/blub.php) and simply being called in the the main template and the .css also being done similar. So I should be fine. Could you tell me how to go about calling the text from tables please mate?

Never done it before so am unsure of how it all works, but pretend I have an index with the link: "read this new article", how would I make the template load that article from the db?

Cheers

[TRshady]

*bump*

someone help me on this please :unsure:

[Nogimics]

OK if you want to pick a certain article out then your going have to have some way of distinguishing which article is what. To do this and I was hoping you would of realised you need to add a column to your table and give each row of data a number. You can either have it Auto Number the rows so you don&#39;t need to add anything or you can give it a number yourself. Either way the column must be made UNIQUE so that no same article has the same Identifier.

I would add a column ID and make it UNIQUE and AUTO INDEX this may mean you have to start the table off again but it should take long.

If you do that each Article or row of data will be easy to pick out as it will have a UNIQUE identifier and you then just use a bit of PHP/MySQL to call to that exact article. if you added this link to your page:

Cool Article Here (article.php?id=1)

That link would send a Variable of: &#036;id = 1 to the article.php page so if the article page was set up to use &#036;id to grab certain article from the database it would know which one to get in that case whichever article has the UNIQUE id of 1.

Get it ?

Set your Database correct and see if you can set up a article.php page.

Article page will need to Connect to Database and the correct table which we know how to do ?? if not look above again. and then only grab 1 article from the database using the variable which is set when you use the link to go to that page. when testing if your page works don&#39;t bother using the link until you know it works. already set a variable in the article page.

ill leave the Article page to you :D as I think I have gave too much help already. you wont learn otherwise :) but Im sure myself or someone else will reply if you get stuck.

Laters

[Nogimics]

Bollocks to it im too nice :D

<?php
//article.php

&#036;id = 1;

&#036;db = mysql_connect("localhost", "triley_test", "test");
mysql_select_db ("triley_content");

&#036;result = mysql_query("select * from software where id = &#036;id");
while(&#036;r=mysql_fetch_array(&#036;result)) {
&#036;section = &#036;r[section];
&#036;title = &#036;r[title];
&#036;content = &#036;r[content];

echo &#036;section;
echo "
";
echo &#036;title;
echo &#036;txt;
echo &#036;content;

}

?>

So in other words all we have done is add a WHERE clause to the SELECT query and it will only pick an article from the table software if there is a column called ID with a VALUE of 1 in it.

Have not tested this as I stopped coding PHP a long time ago so if it dont work then Oooopps :) but it should :rolleyes:

So take the variable out of it once you have tested it and then for each article you want to grab and say look here use the link to it.

BTW there are a lot of things I would do differently in that code but im not gonna start picking out the bad coding bits else we will be here forever :)

[TRshady]

Thank you so much Nogimics ... your a star :D
Knew part of what you said ... but you really did make things clear and Ive managed it&#33;

Click here (http://www.rileysresource.net/dbtest/) to see the little thingy I set up with your help.

Got it quite simple after your first post .... and really am thankfull for putting in the effort to help and doing so. Its people like you and Nakor who make this place so great, looking out and helping each other ... tis real kind.

Cant believe Ive finally managed it ... of course, there are no other things Id like to ask, and only continue helping if wanted ....

Its works great, and with css fdoesnt look to bad either ... but here are a few questions Id love to be answered:

Should a whole site only use one table?
- or should I have a new new table for each category? (reviews, guides etc)

How would I place data from db in a table?
- Have the &#036;title in one cell ... above another cell with the actual content
Have tried making a table using print commands, but then unsure of how to place the data from the table into them.

What did this variable do in the script?: echo &#036;txt;

I&#39;ll get playing with it ... but am off to a good start and thanks again&#33; :D

[Nogimics]

Off to the pub to watch England vs Sweden so if someone wants to point out my coding mistake with the &#036;txt and explain go for it else ill be back later. Probably drunk so the coding will be even worse :lol:

To put the article into a table you can exit PHP at any point on any HTML page.

<?php
//your code up until we echo the article then exit PHP

?>
PUT HTML TABLE HERE (NOW ITHIN THIS TABLE YOU CAN PUT PHP TAGS LIKE:
<table width="100%" border=0 cellspacing=1 cellpadding=1 height=10>
<tr>
<td height=5><?php echo "&#036;section"?></td>
</tr>
</table>
<?php
//back into PHP to stop the query
}
?>

Really got to go now there may be a mistake in that lol but in other words you get to exit PHP then go back into it as long as you open up the <?php tags and close ?>

Will explain better later :) comon England :)

LATERS

[TRshady]

Anyone able to explain the &#036;txt then?
Have just removed it from the script and it still works fine .. so I&#39;ll be without for now, lol. Just tried as you said .. and it worked&#33; :D .....

Right now im tired, so cant think of what else Id love to know (thankfully for you, lol) ... but ill be back, will try out different things. Such as creating a links tables, with different categories .. and a click of &#39;computer sites&#39; for example, brings all links with the category field a computing etc. Thanks again mate .. you&#39;ve made me a happy dude. :D

[h1]

Use different tables for different sections. And just echo out variables to put data in a custom table.

I don&#39;t know why you&#39;re going through the trouble though, I mean, it&#39;s cool to learn, but once you do, find a CMS that&#39;s a lot easier to use. :lol: