new to php, how to pull values from mysql database

[abliss26]

Hi everyone; first, let me say that I'm really not a programmer (although I'm pretty good with scripting and so forth) so please pardon the newbie questions; I have a problem that I'm sure you will be able to help with a solution straight away; I'm running aide in our environment, and am just completing the code to insert the daily reports into a mysql database; no problems with that piece; what I would like to do is to setup a "simple" webpage that would allow the security administrators in our building to display these reports; I've setup the database in the following way; every host has it's own table; each table has 4 columns:
report_id ->auto incrementing integer, primary key
report_text ->longtext which contains the text of each report
report_date ->DATE format is just the date the report was written
report_num ->an integer number used to keep track of records for an entire report; the first report for a day will have a 1 in this record, the second will have a 2, etc.
The mysql and apache servers are the same box; it's a redhat es 3 box. I'm just looking for a simple webpage that will display the reports for the admins; I was thinking of a page in which I'll include a combo box which displays a list of server names (this would be static and would also tell php which table to connect to since tables are named after hosts); next to this list would be a combo box containing list of dates of available reports (report_date column); next to this would be a combo box of a list of available reports (report_number); then maybe just a text filed or something that would display reports text (report_text); thanks very much for your help.

Aaron

[Tastatura]

Hi abliss26 and welcome to Webmaster World.

take a look at the following:
this forum's lib
[webmasterworld.com ]

"Basics of extracting data from MySQL using PHP" thread
[webmasterworld.com ]

PHP/MySQL Tutorial
[webmonkey.com ]

MySQL Functions (in PHP)
[php.net ]

MySQL Documentation
[dev.mysql.com ]

HTH

[abliss26]

Thanks very much for the great links; as I said, I'm a newbie to php, so I'll get started reading the tutorials straight away :)

[abliss26]

Well, I've been doing alot of reading, looking at sample code and so forth, and have learned quite a bit; I found most of the code below from a great tutorial site; What is pretty foreign to me below is the printf statement; I like the look of the report, however it would be better for my purposes if I could customize it so that if the date column ($myrow[2]) was only printed 1 time (instead of for every record), at the start of the table, and so that if I didn't have to print report_id ($myrow[0]); right now I have to include report_id in my select statement, as the syntax of the printf statements is combining the first 2 columns into 1; I'll continue to research printf, however any help here would be helpful, as it seems printf can be quite cryptic...Thanks again very much for your help everyone.

<?php

$title = "Todays report for host1";

include("header.inc");

$result = mysql_query("SELECT report_id, report_text, report_date FROM host1",$db);

echo "<table border=0>\n";

echo "<tr><td>report results</td><td>date</tr>\n";

while ($myrow = mysql_fetch_row($result)) {

printf("<tr><td>%s %s</td><td>%s</tr>\n", $myrow[0], $myrow[1], $myrow[2], $myrow[3]);

}

echo "</table>\n";

include("footer.inc");

?>

[gettopreacherman]

You're looking for a Loop statement...order by date, while the set a variable to compare...if they are the same, don't echo them, if they aren't do...

[abliss26]

I was able to get this to work; table structure remained in tact even after removing report_date and report_id from select statement :) ;I was able to display the date field by doing this fixing up a second printf statement

[abliss26]

I'm very close to achieving my goal :) ; thanks again everyone for your help. Below is what I have so for; here is what I'm missing; I don't understand how to capture values from my text box, combobox and radio buttons; I'm not sure how these are assigned to variables; Also, I'm not sure how to put a sumbmit button onto my page; when I get those 2 pieces, I think I'll be home free; I've included a hard coded example that works; please help and thanks again for working thru this with me.

<html>
<body>

<form method="post" action="<?php echo $PHP_SELF;?>">
Enter date in format yyyy-mm-dd:<input type="text" size="12" maxlength="10" name="mydate">:<br />

Select host to query:<br />
<select name="host">
<option value="host1">host1</option>
<option value="host2">host2</option>
<option value="host3">host3</option></select>:<br />

Select which report you wish to view::<br />
report1:<input type="radio" value="report 1" name="reportnum">:<br />
report2:<input type="radio" value="report 2" name="reportnum">:<br />

<?php

//this is hard coded but will really just be the value returned from host combo box above
$title = "Report for ms-lnx-s08";
include("header.inc");

//select a table to use based upon value returned from host combo box above
$mytable='MS_LNX_S08';
//select a report to view for the day, this will also be retrived from above
$repnum='1';
//get todays date for the report
//$mydat1=date("Y-m-d");
$mydat1='2006-04-10';
//query for our data
$result = mysql_query("SELECT report_text FROM $mytable WHERE report_date='$mydat1' AND report_num='$repnum'",$db);
$mydatr = mysql_query("SELECT * FROM $mytable WHERE report_date='$mydat1' AND report_num='$repnum'",$db);

printf("report query ran for %s<br>\n", mysql_result($mydatr,0,"report_date"));
echo "<table border=0>\n";

echo "<tr><td>report results</td><td></tr>\n";

while ($myrow = mysql_fetch_row($result)) {
printf("<tr><td>%s %s</td><td>%s</tr>\n", $myrow[0], $myrow[1], $myrow[2], $myrow[3]);

}

echo "</table>\n";

include("footer.inc");

?>
</body>

</html>

[abliss26]

Well, I've made some more progress (I think); I've got a submit button used to run the database query, however I'm not sure why, but my sumbit button doesn't do anything; I'm not to the point where I can pass variables to the search string, so I just wanted to test my button using code that should display results; I would have expected that clicking submit would run the hard coded query and display the table below the form, but that doesn't seem to work; Any ideas? Thanks

<html>
<body>
<?php

if ($submit) {
// process form
//this is hard coded but will really just be the value returned from host combo box above
$title = "Report for host1";
include("header.inc");

//select a table to use based upon value returned from host combo box above
$mytable='host1';
//select a report to view for the day, this will also be retrived from above
$repnum='1';
//get todays date for the report
//$mydat1=date("Y-m-d");
$mydat1='2006-04-11';
//$mydat1='$mydate'
//query for our data
$result = mysql_query("SELECT report_text FROM $mytable WHERE report_date='$mydat1' AND report_num='$repnum'",$db);
$mydatr = mysql_query("SELECT * FROM $mytable WHERE report_date='$mydat1' AND report_num='$repnum'",$db);

printf("report query ran for %s<br>\n", mysql_result($mydatr,0,"report_date"));
echo "<table border=0>\n";

echo "<tr><td>report results</td><td></tr>\n";

while ($myrow = mysql_fetch_row($result)) {
printf("<tr><td>%s %s</td><td>%s</tr>\n", $myrow[0], $myrow[1], $myrow[2], $myrow[3]);

}

echo "</table>\n";
//include("footer.inc");
} else{

// display form

?>

<form method="post" action="<?php echo $PHP_SELF?>">

<form method="post" action="<?php echo $PHP_SELF;?>">
Enter date in format yyyy-mm-dd:<input type="text" size="12" maxlength="10" name="mydate">:<br />

View report for server:<br />
<select name="host">
<option value="host1">host1</option>
<option value="host2">host2</option>
<option value="host3">host3</option>
<option value="host4">host4</option></select>:<br />

Select which report you wish to view::<br />
report1:<input type="radio" value="report 1" name="reportnum">:<br />
report2:<input type="radio" value="report 2" name="reportnum">:<br />

<input type="submit" value="submit" name="submit">

</form>

<?php
} // end if
?>
</body>
</html>

[abliss26]

Well, i couldn't get this to work so that the report would appear on the same page, but is working great when the form sends data to an outside php script, which for my purposes is good enough, thanks evyerone for your help.