asked on

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

Ray Paseur

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.

ukerandi

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 " )

eezar21

ASKER

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

Ray Paseur

Is "refer" the column you want to search on?

Ray Paseur

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

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

eezar21

ASKER

Thankyou so much - I am getting there :)