PHP database querys?

[fireydeviant]

Ive figured out how too connect, and how to print something with:

<?php
/* Connecting, selecting database */
$link = mysql_connect("mysql_host", "mysql_user", "mysql_password")
   or die("Could not connect : " . mysql_error());
echo "Connected successfully";
mysql_select_db("my_database") or die("Could not select database");

/* Performing SQL query */
$query = "SELECT * FROM my_table";
$result = mysql_query($query) or die("Query failed : " . mysql_error());

/* Printing results in HTML */
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   echo "\t<tr>\n";
   foreach ($line as $col_value) {
       echo "\t\t<td>$col_value</td>\n";
   }
   echo "\t</tr>\n";
}
echo "</table>\n";

/* Free resultset */
mysql_free_result($result);

/* Closing connection */
mysql_close($link);
?>

I have modified it to fit my needs of course. what im trying to do is to make a new page that gets all its info from a certain catagory(called news) and prints it out along with the posters name.

This catagory is phpbb_posts_text(holds the text/post name) and phpbb_posts(holds post ID poster ID etc.). Then phpbb_users has the user id, which is the same as the poster ID. Im not sure how to make it say the Title, the date, the poster, and then put the text under that.

[Asterix]

<?php

/* Performing SQL query */
$query = "SELECT * FROM my_table";
$result = mysql_query($query) or die("Query failed : " . mysql_error());

/* Printing results in HTML */
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   echo "\t<tr>\n";
   foreach ($line as $col_value) {
       echo "\t\t<td>$col_value</td>\n";
   }
   echo "\t</tr>\n";
}
echo "</table>\n";

Fiery the key is in this part. Let me explain how this works

1) The statement

$line = mysql_fetch_array($result,MYSQL_ASSOC)

If u have a result with multiple rows and mutiple columns in DB then, with each call $line will get the next row and if all rows are already called then ull get false so the while loop will give all the rows one each time
2) Now, u call with MYSQL_ASSOC it means it will return the result with associative indexes, i.e, u can access the column elements like this

$col1 = $ line["Title"];

Where title is the name of column

3) If u have multiple columns then using inner for loops is really not gr8 idea here is example on how i extract it(there can be better way i am newbie started two days ago)

while($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
	printf("Name : %s <br>\n",$row["name"]);
	printf("Hall : %s <br>\n",$row["hall_nm"]);
	printf("Room : %s <br>\n",$row["room"]);
	printf("Hall dues : %d <br>\n",$row["dues"]);
}

Hope this helps

[Asterix]

hey if u could post ur modified code then my noob brain can think clearer if I am completly off with above post

[greffov]

This catagory is phpbb_posts_text(holds the text/post name) and phpbb_posts(holds post ID poster ID etc.). Then phpbb_users has the user id, which is the same as the poster ID. Im not sure how to make it say the Title, the date, the poster, and then put the text under that.

SELECT * FROM phpbb_posts_text, phpbb_posts, phpbb_users WHERE phpbb_posts_text.postid=phpbb_posts.id AND phpbb_posts.userid=phpbb_users.id AND [some selection which gets out the right thread]

then user Asterix great post to get it on the page.

[JamesT]

Originally posted by fireydeviant
Ive figured out how too connect, and how to print something with:

<?php
/* Connecting, selecting database */
$link = mysql_connect("mysql_host", "mysql_user", "mysql_password")
   or die("Could not connect : " . mysql_error());
echo "Connected successfully";
mysql_select_db("my_database") or die("Could not select database");

/* Performing SQL query */
$query = "SELECT * FROM my_table";
$result = mysql_query($query) or die("Query failed : " . mysql_error());

/* Printing results in HTML */
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   echo "\t<tr>\n";
   foreach ($line as $col_value) {
       echo "\t\t<td>$col_value</td>\n";
   }
   echo "\t</tr>\n";
}
echo "</table>\n";

/* Free resultset */
mysql_free_result($result);

/* Closing connection */
mysql_close($link);
?>

Ah man, that gives me a headache. This is how i code . Keep it simple :

$db = mysql_connect(localhost, user, pass);
mysql_select_db(database);

$sql = mysql_query("SELECT * FROM table WHERE something");
echo mysql_error();

GTG, but i post back later.

[greffov]

Originally posted by JamesT
Ah man, that gives me a headache. This is how i code . Keep it simple :

$db = mysql_connect(localhost, user, pass);
mysql_select_db(database);

$sql = mysql_query("SELECT * FROM table WHERE something");
echo mysql_error();

GTG, but i post back later.

LOL, it was just a copy/paste excercise from the online manual, my first DB app, looked exactly like that