Page 1 of 1
display certain records..
Posted: Thu Oct 16, 2008 7:03 am
by ma5ect
Hi all,
I have a recordset to display records from database, but i dont want all the records to be displayed, i would only like the record for which the user has requested for..eg student number..
any ideas?
code:
Code: Select all
mysql_select_db($database_mysql_connect, $mysql_connect);
$query_Recordset1 = "SELECT * FROM robs_enterprise";
$Recordset1 = mysql_query($query_Recordset1, $mysql_connect) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
Re: display certain records..
Posted: Thu Oct 16, 2008 7:08 am
by papa
$query_Recordset1 = "SELECT student_number FROM robs_enterprise";
or
$query_Recordset1 = "SELECT * FROM robs_enterprise WHERE student_number = '$user_input'";
You mean something like that ?
Re: display certain records..
Posted: Thu Oct 16, 2008 7:09 am
by aceconcepts
The WHERE clause can do this for you:
Code: Select all
$query_Recordset1 = "SELECT * FROM robs_enterprise WHERE student_number='$student_number'";
Re: display certain records..
Posted: Thu Oct 16, 2008 7:14 am
by ma5ect
what i want to achieve is, i want the user to enter their number and then the database to display the records for only that number they entered...am using dreamweaver cs3
Re: display certain records..
Posted: Thu Oct 16, 2008 7:15 am
by papa
Then use aceconcepts' code.

Re: display certain records..
Posted: Thu Oct 16, 2008 7:17 am
by aceconcepts
Ok, so the two replies you received should work for you.
Code: Select all
//Get the input passed by the user
$student_number=$_POST['student_number'];
//Get the records specific to this student number
$query_Recordset1 = "SELECT * FROM robs_enterprise WHERE student_number='$student_number'";
Re: display certain records..
Posted: Thu Oct 16, 2008 7:28 am
by ma5ect
I am little confused as to where, which code goes...
full code:
Code: Select all
<?php require_once('file:///C|/xampp/htdocs/Connections/mysql_connect.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO robs_enterprise (ID, `Student number`, `Pathway / Course`, `Essay only`, `Marking tutor name`, `Module unit title`, `Module number`, `Assignment title`, `Time`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
GetSQLValueString($_POST['ID'], "int"),
GetSQLValueString($_POST['Student_number'], "int"),
GetSQLValueString($_POST['Pathway__Course'], "text"),
GetSQLValueString($_POST['Essay_only'], "text"),
GetSQLValueString($_POST['Marking_tutor_name'], "text"),
GetSQLValueString($_POST['Module_unit_title'], "text"),
GetSQLValueString($_POST['Module_number'], "text"),
GetSQLValueString($_POST['Assignment_title'], "text"),
GetSQLValueString($_POST['Time'], "date"));
mysql_select_db($database_mysql_connect, $mysql_connect);
$Result1 = mysql_query($insertSQL, $mysql_connect) or die(mysql_error());
}
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO robs_enterprise (`Student number`) VALUES (%s)",
GetSQLValueString($_POST['Student_number'], "int"));
mysql_select_db($database_mysql_connect, $mysql_connect);
$Result1 = mysql_query($insertSQL, $mysql_connect) or die(mysql_error());
}
mysql_select_db($database_mysql_connect, $mysql_connect);
$query_Recordset1 = "SELECT robs_enterprise.`Student number` FROM robs_enterprise";
$Recordset1 = mysql_query($query_Recordset1, $mysql_connect) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<p> </p>
<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
<table align="center">
<tr valign="baseline">
<td nowrap="nowrap" align="right">Student number:</td>
<td><input type="text" name="Student_number" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right"> </td>
<td><input type="submit" value="Insert record" /></td>
</tr>
</table>
<input type="hidden" name="MM_insert" value="form1" />
</form>
<p> </p>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>
Re: display certain records..
Posted: Thu Oct 16, 2008 7:37 am
by aceconcepts
Well, before you execute your query will obviously need a value to query with (student_number).
So, what you could do is check whether the "submit" button has been clicked and then if it has, execute the query:
Before you do the following, you need to give your "Insert Record" button a name (e.g. name="submit")
Code: Select all
if(isset($_POST['submit']))
{
//Get the input passed by the user
$student_number=$_POST['student_number'];
//Get the records specific to this student number
$query_Recordset1 = "SELECT * FROM robs_enterprise WHERE student_number='$student_number'";
}
Re: display certain records..
Posted: Thu Oct 16, 2008 7:43 am
by ma5ect
where do i place dis code in the above code??
Re: display certain records..
Posted: Thu Oct 16, 2008 8:17 am
by aceconcepts
This is how your code should look:
Code: Select all
<?php require_once('file:///C|/xampp/htdocs/Connections/mysql_connect.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO robs_enterprise (ID, `Student number`, `Pathway / Course`, `Essay only`, `Marking tutor name`, `Module unit title`, `Module number`, `Assignment title`, `Time`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
GetSQLValueString($_POST['ID'], "int"),
GetSQLValueString($_POST['Student_number'], "int"),
GetSQLValueString($_POST['Pathway__Course'], "text"),
GetSQLValueString($_POST['Essay_only'], "text"),
GetSQLValueString($_POST['Marking_tutor_name'], "text"),
GetSQLValueString($_POST['Module_unit_title'], "text"),
GetSQLValueString($_POST['Module_number'], "text"),
GetSQLValueString($_POST['Assignment_title'], "text"),
GetSQLValueString($_POST['Time'], "date"));
mysql_select_db($database_mysql_connect, $mysql_connect);
$Result1 = mysql_query($insertSQL, $mysql_connect) or die(mysql_error());
}
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO robs_enterprise (`Student number`) VALUES (%s)",
GetSQLValueString($_POST['Student_number'], "int"));
mysql_select_db($database_mysql_connect, $mysql_connect);
$Result1 = mysql_query($insertSQL, $mysql_connect) or die(mysql_error());
}
mysql_select_db($database_mysql_connect, $mysql_connect);
if(isset($_POST['submit']))
{
//Get the input passed by the user
$student_number=$_POST['student_number'];
//Get the records specific to this student number
$query_Recordset1 = "SELECT * FROM robs_enterprise WHERE student_number='$student_number'";
$Recordset1 = mysql_query($query_Recordset1, $mysql_connect) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
}
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<p> </p>
<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
<table align="center">
<tr valign="baseline">
<td nowrap="nowrap" align="right">Student number:</td>
<td><input type="text" name="Student_number" value="" size="32" /></td>
</tr>
<tr valign="baseline">
<td nowrap="nowrap" align="right"> </td>
<td><input type="submit" value="Insert record" /></td>
</tr>
</table>
<input type="hidden" name="MM_insert" value="form1" />
</form>
<p> </p>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>
Re: display certain records..
Posted: Thu Oct 16, 2008 8:30 am
by ma5ect
dis code doesnt display results from the database, instead it inserts a new record..
Code: Select all
<?php require_once('Connections/mysql_connect.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO robs_enterprise (`Student number`) VALUES (%s)",
GetSQLValueString($_POST['Student_number'], "int"));
mysql_select_db($database_mysql_connect, $mysql_connect);
$Result1 = mysql_query($insertSQL, $mysql_connect) or die(mysql_error());
}
mysql_select_db($database_mysql_connect, $mysql_connect);
$query_Recordset1 = "SELECT robs_enterprise.`Student number` FROM robs_enterprise";
$Recordset1 = mysql_query($query_Recordset1, $mysql_connect) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
mysql_select_db($database_mysql_connect, $mysql_connect);
if(isset($_POST['submit']))
{
//Get the input passed by the user
$student_number=$_POST['student_number'];
//Get the records specific to this student number
$query_Recordset1 = "SELECT * FROM robs_enterprise WHERE student_number='$student_number'";
$Recordset1 = mysql_query($query_Recordset1, $mysql_connect) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
}
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<p> </p>
<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
<table align="center">
<tr valign="baseline">
<td nowrap="nowrap" align="right"><input type="text" name="Student number" id="Student number" /></td>
<td><input type="submit" value="Submit" /></td>
</tr>
</table>
<label></label>
<input type="hidden" name="MM_insert" value="form1" />
</form>
<p> </p>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>
Re: display certain records..
Posted: Thu Oct 16, 2008 9:09 am
by onion2k
Read it. Learn it. Understand it. Search for some of the terms you don't know yet in the PHP or MySQL manuals. You'll have to do some work to learn about what people are telling you. Don't just run it and then come back to say it's not worked. That doesn't help anyone.
One day I'm going to post some code that drops a user's database just to see if they read it and try to understand it before complaining that it's not worked first time...

Re: display certain records..
Posted: Thu Oct 16, 2008 9:19 am
by aceconcepts
I like the approach onion2k
