Making sense of MySQL table

Hi,

I have a database that I have set up which is being fed data from a pixel I have on my site, to tell me where my users are coming from, the time they are on the site, the page they are on etc.

I've now in the past couple of days massed up a load of data, which I can display using the PHP code below.  This is clearly starting to become quite unwieldy.  What I would ideally like to be able to do is to be able to create an interface that I can select variables to just show a summary of the number of finds and then the data presented in a table (as I have done below)

I have database columns of id, time, url, user agent and ip address.  I'm stuck on how to use what is input into the POST field I have and using that to sort and display the rows that this is related to.  When it comes to the URL I would need to segment the URLs e.g. be able to enter the page i.e. news and then look for where the URL string CONTAINS that word.

Sorry if this is way over complicated (it seems so to me) but I would really appreciate any pointers (or assistance).  Thanks in advance

A
<html> 
<head>

<head>

<title>Your Page Title</title></head> 
<body> 


<?php
if(!isset($_POST['find']))
{
?>
<form method = "post" action = "<?php echo $_SERVER['PHP_SELF'];?>">
<table width = "450" align = "center">
	<tr>
		<td><b><i>Please select <strong class="highlight">site</strong> in the field below </i></b></td>
	</tr>
	<tr>
		<td>
		From&nbsp;:&nbsp;
		<input type = "text" name = "refer">
		&nbsp;:&nbsp;
		
	</tr>
	<tr>
		<td align = "center">
			<input type = "submit" name = "find" value = "SEARCH">
			<input type = "reset" value = "CLEAR FORM">
		</td>	
	</tr>
</table>
</form>





<?php 
$username="user";
$password="password";
$database="database";

mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$site = trim($_POST['refer']);

$result = mysql_query( "SELECT * FROM data" ) 
or die("SELECT Error: ".mysql_error()); 
$num_rows = mysql_num_rows($result); 
print "There are $num_rows records.<P>"; 
print "<table width=400 border=1>\n"; 
while ($get_info = mysql_fetch_row($result)){ 
print "<tr>\n"; 
foreach ($get_info as $field) 
print "\t<td><font face=arial size=1/>$field</font></td>\n"; 
print "</tr>\n"; 
} 
print "</table>\n"; 
  }

?>    


</body> 
</html>

Open in new window

eezar21Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Here are a couple of introductory things about PHP and MySQL (hint: the SitePoint book is a "must-have").
http://php.net/tut.php
http://www.sitepoint.com/books/phpmysql4/

Please post the CREATE TABLE statement for the table named "data" and I will try to give you some assistance with this.  MySQL has a wild-card system.  You can use the reserved word LIKE to make partial matches on columns.
0
ukerandiCommented:
in the select statement you can use
$result = mysql_query( "SELECT * FROM data where TablenameFiled='$site'" )

TablenameFiled- enter tablefield name
 if you need to sort

$result = mysql_query( "SELECT * FROM data where TablenameFiled='$site' order by TablenameFiled " )
0
eezar21Author Commented:
Thanks both of you.

Ray:  I have posted the create table code below...appreciate the help

Ukerandi:  So, I can understand to select from - however I am trying to write a page with fields which when filled in will return the information I need, rather than having to go and adjust the code each time.

Thanks

A
<?php

$user="user";
$password="password";
$database="database";




mysql_connect('localhost',$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="CREATE TABLE data2(
id int(6) NOT NULL auto_increment,
time varchar(20) NOT NULL,
refer varchar(100) NOT NULL, 
agent varchar(100) NOT NULL,
ip varchar(30) NOT NULL, 
PRIMARY KEY (id),
UNIQUE id (id),
KEY id_2 (id))";

mysql_query($query);
mysql_close();

echo"database table created " ;
?>

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ray PaseurCommented:
Is "refer" the column you want to search on?
0
Ray PaseurCommented:
I will have to leave in a moment, but here are a couple of (hopefully) useful things.  First this. It is my teaching example that shows how we do some of the basics in PHP and MySQL.
<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF THE SCRIPT GETS THIS FAR IT CAN DO QUERIES




// ESCAPE AN EXTERNAL DATA FIELD FOR USE IN MYSQL QUERIES
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, SHOW THE ERROR
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = mysql_num_rows($res);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/>$sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $num ROWS OF DATA ";
    echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
while ($row = mysql_fetch_assoc($res))
{
    var_dump($row);
}




// A WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
$sql = "SELECT COUNT(*) FROM my_table";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];
$fmt = number_format($num);
echo "<br/>THERE ARE $fmt ROWS IN THE TABLE";




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES ('$safe_username')";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
$id  = mysql_insert_id($db_connection);
echo "<br/>YOU JUST INSERTED A RECORD WITH AUTO_INCREMENT ID = $id";

Open in new window

0
Ray PaseurCommented:
Next this.  I think I would write the script something like this.  Obviously I cannot test it because I do not have your data base, but it should be correct in principle.  

Since this is an informational query only, you would want to use $_GET (URL Parameter) instead of $_POST.

I'll check back later today.  All the best, ~Ray
<?php // RAY_temp_eezar21.php
error_reporting(E_ALL);

// INITIALIZE THE DATA BASE CONNECTION AND SELECTION
$username="user";
$password="password";
$database="database";

mysql_connect('localhost',$username,$password);
mysql_select_db($database) or die( "Unable to select database");

// INITIALIZE THE SEARCH STRING
$q = isset($_GET['q']) ? $_GET['q'] : NULL;

// IF THERE IS A QUERY STRING
if ($q)
{
    // MAKE THE QUERY STRING SAFE FOR USE IN A QUERY
    $s_q = mysql_real_escape_string($q);

    // CREATE A QUERY AND RUN IT
    $sql = "SELECT time, refer, ip FROM data WHERE refer LIKE '%$s_q%' ORDER BY refer";
    $res = mysql_query($sql);
    if (!$res) die( "<br/>FAIL: $sql<br/>" . mysql_error() );

    // HOW MANY ROWS OF DATA MATCH THE QUERY STRING
    $num = mysql_num_rows($res);
    echo "<h3>$num ROWS MATCH <b>$q</b>" . PHP_EOL;

    // CREATE A TABLE OF THE MATCHING ROWS
    echo '<table width="400" border="1">' . PHP_EOL;

    // CREATE THE TITLE ROW USING THE COLUMN NAMES
    $row = mysql_fetch_assoc($res);
    echo "<tr>";
    foreach ($row as $column_name => $nothing)
    {
        echo "<td>" . htmlentities($column_name) . "</td>";
    }
    echo "</tr>" . PHP_EOL;

    // RESET THE DATA POINTER SO WE CAN GET ALL THE ROWS
    mysql_data_seek($res, 0);

    // GET EACH OF THE ROWS
    while ($row = mysql_fetch_assoc($res))
    {
        echo '<tr>';
        foreach ($row as $column_name => $value)
        {
            echo "<td>" . htmlentities($value) . "</td>";
        }
        echo '</tr>' . PHP_EOL;
    }

    // CLOSE OUT THE TABLE
    echo '</table>' . PHP_EOL;
}


$html = <<<HTML
<html>
<head>
<title>SEARCH $q</title></head>
<body>
<form method="get">
<table width="450" align="center">
	<tr>
		<td><b><i>Please enter a substring of the <strong class="highlight">site URL</strong> in the field below </i></b></td>
	</tr>
	<tr>
		<td>
		From&nbsp;:&nbsp;
		<input type="text" name="q" value="$q" />
		&nbsp;:&nbsp;

	</tr>
	<tr>
		<td align="center">
			<input type="submit" value="SEARCH" />
		</td>
	</tr>
</table>
</form>
HTML;

echo $html;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eezar21Author Commented:
Thankyou so much - I am getting there :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.