PHP - Mysql Only Getting The First Record?

Greetings everyone! I have a form that stores data to a database. Separate from that page I would like to have another where at any given time a user can download a .csv of the most recent addition or two? If not a .csv, can they easily view the record without having to know mySQL / phpAdmin and such?

Thanks!

Jerry

Hi All,

Please can someone help me with this bit of code. I have a mysql database with a table called 'events'. I have six fields namely 'ID, mxitID, type, message, ts, publish'.
I am trying to update the field 'publish' from Yes --> No or if it is already No then back to Yes again. I have two files then main one beacon.php and publish.php
Here is the code:

beacon.php
Code: [Select]
<?php
        // connect to the database
        include('connect-db.php');

        // display data in table
//        echo "<p><b>View All</b> | <a href='view-paginated.php?page=1'>View by Page</a></p>";
//   echo "Select your Carousel: ";
   
   // get results from database
        $result = mysql_query("SELECT * FROM events WHERE type='beacon'")
                or die(mysql_error());

        echo "<table border='1' cellpadding='10'>";
   echo "<a href='index.php'>b Back</a><br><br>";
        echo "<tr> <th>ID</th> <th>mxitID</th> <th>Type</th> <th>Message</th> <th>Timestamp</th> <th>Published</th> <th>Modify</th></tr>";

        // loop through results of database query, displaying them in the table
        while($row = mysql_fetch_array( $result )) {
                
                // echo out the contents of each row into a table
                echo "<tr>";
                echo '<td>' . $row['ID'] . '</td>';
                echo '<td>' . $row['mxitID'] . '</td>';
                echo '<td>' . $row['type'] . '</td>';
      echo '<td>' . $row['message'] . '</td>';
      echo '<td>' . $row['ts'] . '</td>';
      echo '<td>' . $row['publish'] . '</td>';
                echo '<td><a href="publish.php?id=' . $row['ID'] . '"><input name="update" type="submit" id="update" value="Update"></a></td>';
                echo "</tr>"; 
        } 
        // close table>
        echo "</table>";
?>

publish.php
<?php

 // connect to the database
 include('connect-db.php');

 // get id value
 $id = $_GET['id'];
echo "$id";

 $sql="SELECT * FROM events WHERE type='beacon'";
$result=mysql_query($sql);


$active=($result['publish']=='')?'Yes':'No';
echo "$active";

if ($active='Yes') {
    $active='No';
 } else {
    $active='Yes';
}
$sql = "UPDATE events SET publish='$active' WHERE ID='$id'";
$result = mysql_query($sql);

// header("Location: beacon.php");
?>

I cant seem to change the value...any help would be much appreciated. Thank you in advance.

MOD EDIT: code tags added.

I have a MySQL database with each record of a person who has registered for an event, I am displaying the information on a web page for a user, but he wants to be able to print out all the records in alphabetical order by last name, first name later on so he will have a hard copy of each person who has registered at the table when they arrive.  How can I write each record to a Txt file that he can print out later that will be formated with the record contents along with each fields definition (Ex. Last Name - Smith, First Name - John, etc)?

I have a form that submits a record and saves it to the database, I've got that working already, I'm trying to figure out if there is a cleaner way to delete a record with a button that gets a value from the unique key, in this case 'id'
here is how the delete button looks like:

   // get info from table
$query  = "SELECT id, Title, Message FROM table_name";
$result = mysql_query($query);

//displaying all data
while($row = mysql_fetch_assoc($result))
{
echo"
<div class='status'><h3>{$row['Title']} <br></h3>" . "
         <h5>{$row['Message']} <br><br></h5>"; 
 $id = $row['id']; //trying to get unique key from database
//delete button
 echo "</div>
 <form action='delete.php' method='post' />
 <input type='hidden' name='delete' value='yes' /> 
 <input type='hidden' name='id' value='$id' />
 <input type='submit' value='remove' /></form>";
}

As you can see, i'm trying to give $id a unique value but for some reason i'm not getting it.
The delete.php code looks like this:

if (isset($_POST['delete']))// check if delete was clicked
        {
         $query = "DELETE FROM table_name WHERE id='$id'";
echo "$id Deleted successfully";
}
elseif(!mysql_query($query, $db_server))
{
echo "DELETE failed: $query<br />" .
myql_error() . "<br /><br />";
         }
mysql_close();

I'm new to php and still learning so, if you think there are other ways to do this, please let me know, the button won't do anything to the data.

Thanks in advance

Hey Everyone, I want to start out by saying thanks in advance for reading this. Since I started working with the XAMPP package this community has been a great resource, though this is my first post.  most of my work has been modifying existing stuff in our network, this is my first project from scratch.

I am working on an access control project where I am storing site names and RFID tags in a table, and using PHP to check for existing records. Basically, I want to submit 2 credentials from a host (site and RFID), check if they exist together in the table, if so return TRUE or 1, if not return FALSE or 0. I am new to both PHP and MySQL (although I took a course years ago) so I am looking here for help.

On this server I have XAMPP installed and everything I need running. I have a table and created a few records for testing. My current trouble is getting PHP to return the proper values for the SQL query. The SQL query works fine, I think - it returns the number of rows that match and that record.  I'm not sure this is the result I need to be able to deal with the way I want.  Using other resources my current PHP statement looks like this:
Code: [Select]
if(mysql_num_rows(mysql_query("SELECT * FROM `users` WHERE 'site_name' LIKE 'berland' AND 'card_id' LIKE '290093C84E' LIMIT 0 , 30"))>0){echo '1';}
   else
   {echo '0';} So, my table has the entry as noted in this query above. Final one will be dynamic where the host will be sending those values - I will likely need help with that as well, but small steps first.;-) Anyway, I was playing around with the end statement >0{echo '1';} but wasn't able to make it do what I want.
Any help is appreciated. Thank you.

Aaron

Dear all,

I would like to find the total number of individual record in MySQL with php.

For example:
I have different department name stored in a column in MySQL.
I would like to find out the total number of each department.

But I do not know the names of the department

How can I do it?? Counting the total of each department without knowing their names

Thanks!

I am trying to set up a item entry page form.png:

Upon submission it shows unsuccessful even though I have checked the fields on mysql table and seem to be good am I missing something?

Code: [Select]
<form action="" method="post" enctype="multipart/form-data" name="Product_Entry">
<TABLE>
<TR>
<TD>Product ID</TD><TD><input name="SKU_ProductID" value="<?php if (isset($_post['SKU_ProductID'])) echo $_POST['SKU_ProductID']; ?>" type="text" size="11" maxlength="11" /></TD>
</TR>
<TR>
<TD>Merchant SKU ID</TD><TD><input name="SKU_MerchSKUID" value="<?php if (isset($_post['SKU_MerchSKUID'])) echo $_POST['SKU_MerchSKUID']; ?>" type="text" size="18" maxlength="30" /></TD>
</TR>
<TR>
<TD>Game Title</TD><TD><input name="Game_Title" value="<?php if (isset($_post['Game_Title'])) echo $_POST['Game_Title']; ?>" type="text" size="40" maxlength="40" /></TD>
</TR>
<TR>
<TD>Platform</TD><TD><input name="Platform" value="<?php if (isset($_post['Platform'])) echo $_POST['Platform']; ?>" type="text" size="20" maxlength="20" /></TD>
</TR>
<TR>
<TD>Genre</TD><TD><input name="Genre" value="<?php if (isset($_post['Genre'])) echo $_POST['Genre']; ?>" type="text" size="11" maxlength="11" /></TD>
</TR>
<TR>
<TD>Weight</TD><TD><input name="Weight" value="<?php if (isset($_post['Weight'])) echo $_POST['Weight']; ?>" type="text" size="7" maxlength="7" /></TD>
</TR>
<TR>
<TD>Supplier</TD><TD><input name="Supplier" Value="<?php if (isset($_post['Supplier'])) echo $_POST['Supplier']; ?>" type="text" size="25" maxlength="25" /></TD>
</TR>
<TR>
<TD>Suppliers Price</TD><TD><input name="Supplier_Price" value="<?php if (isset($_post['Supplier_Price'])) echo $_POST['Supplier_Price']; ?>" type="text" size="10" maxlength="12" /></TD>
</TR>
<TR>
<TD>Cashback</TD><TD><input name="Cashback" value="<?php if (isset($_post['Cashback'])) echo $_POST['Cashback']; ?>" type="text" size="6" maxlength="8" /></TD>
</TR>
<TR>
<TD>Cashback Amount</TD><TD><input name="Cashback_Amount" value="<?php if (isset($_post['Cashback_Amount'])) echo $_POST['Cashback_Amount']; ?>" type="text" size="7" maxlength="11" /></TD>
</TR>
<TR>
<TD><input type="hidden" name="submitted" value="true"/></TD><TD><input name="Submit" type="submit" value="Add_Product" /></TD>
</TR></form></TABLE>

<?php # Product Entry

$page_title = 'Product Entry';

if (isset($_POST['Submit'])) {

$errors = array();

if (empty($_POST['SKU_ProductID'])) {
$errors[] = 'Please enter Product ID.';
} else {
$sid = trim($_POST['SKU_ProductID']);
}

if (empty($_POST['SKU_MerchSKUID'])) {
$errors[] = 'Please enter Merchant SKU.';
} else {
$mid = trim($_POST['SKU_MerchSKUID']);
}

if (empty($_POST['Game_Title'])) {
$errors[] = 'Please enter Game Title.';
} else {
$gt = trim($_POST['Game_Title']);
}

if (empty($_POST['Platform'])) {
$errors[] = 'Please enter Platform.';
} else {
$pl = trim($_POST['Platform']);
}

if (empty($_POST['Genre'])) {
$errors[] = 'Please enter Genre.';
} else {
$ge = trim($_POST['Genre']);
}

if (empty($_POST['Weight'])) {
$errors[] = 'Please enter Weight.';
} else {
$we = trim($_POST['Weight']);
}

if (empty($_POST['Supplier'])) {
$errors[] = 'Please enter Supplier.';
} else {
$sup = trim($_POST['Supplier']);
}

if (empty($_POST['Supplier_Price'])) {
$errors[] = 'Please enter Supplier Price.';
} else {
$sp = trim($_POST['Supplier_Price']);
}

if (empty($_POST['Cashback'])) {
$errors[] = 'Please enter Cashback %.';
} else {
$cb = trim($_POST['Cashback']);
}

if (empty($_POST['Cashback_Amount'])) {
$errors[] = 'Please enter Cashback Amount.';
} else {
$cba = trim($_POST['Cashback_Amount']);
}

if (empty($errors)) {

require_once ('connect.php');

[b]$q = "INSERT INTO `Products` (`SKU_ProductID`, `SKU_MerchSKUID`, `Game_Title`, `Platform`, `Genre`, `Weight`, `Supplier`, `Supplier_Price`, `Cashback`, `Cashback_Amount`) VALUES ('$sid', '$mid', '$gt', '$pl', '$ge', '$we', '$sup', '$sp', '$cb', '$cba')";
$r = @mysql_query ($dbc, $q);

if ($r) { // If it ran OK.

// Print a message:
echo '<h1>Thank you!</h1>
<p>Product Inserted!</p><p><br /></p>';

} else { // If it did not run OK.

// Public message:
echo '<h1>System Error</h1>
<p class="error">You could not be registered due to a system error. We apologize for any inconvenience.</p>'; 

// Debugging message:
echo '<p>' . mysqli_error($dbc) . '<br /><br />Query: ' . $q . '</p>';

} // End of if ($r) IF.

mysqli_close($dbc); // Close the database connection.[/b]
// Include the footer and quit the script:
include ('includes/footer.html'); 
exit();

} else {

echo '<H1>Error!</H1>
<p class="error">The Following error(s) occurred:<br />';

foreach ($errors as $msg) {

echo " - $msg<br />\n";
}

echo '</p><p>Please try again.</p><p><br /></p>';

}

}

?>
If there is anything else needed let me know

Here is what I am trying to accomplish:

I have a students table with a studentID
I also have a notes table and a sched table with studentID

The sched table is working as planned where if the student is scheduled for more than one time he is displayed twice.
However if there are multiple notes per student the student is display for each individual note where I would like only the most current note to be displayed

Here is what the output is:

1- 10:30:00 - 10:50:00   student3 Three   FST   Teacher One   Special1 One   Writing Ratios   09/10
2- 10:30:00 - 10:50:00   student3 Three   FST   Teacher One   Special1 One   Needs to work on fractions and decimals   09/10
3- 13:00:00 - 14:00:00   student3 Three   FST   Teacher One   Special1 One   Writing Ratios   09/10
4- 13:00:00 - 14:00:00   student3 Three   FST   Teacher One   Special1 One   Needs to work on fractions and decimals   09/10

As you can see I have two notes and two schedule times for this student. what I want it to display is only line 1 & 3. which is the newest note in the system.

Below is my code that I am using. Any help would be greatly appreciated.


Code: [Select]
<?php 
$result = mysql_query("SELECT * FROM students 
  LEFT JOIN teachers ON students.teacherId = teachers.teacherId
  LEFT JOIN course ON students.courseId = course.courseId
  LEFT JOIN specialEd ON students.specialId = specialEd.specialId
  LEFT JOIN sched ON students.studentId = sched.studentId 
  ORDER BY start
  ");
echo "<table>";
while ($row = mysql_fetch_array($result)){
$id = "?id=" . $row['studentId'];
echo "<tr>";
echo "<td>" . $row['start'] . " - " . $row['stop'] . "</td>";
echo "<td>" . "<a href='student.php$id'>" . $row['fName'] . " " . $row['lName'] . "</td>";
echo "<td>" . $row['courseName'] . "</td>";
echo "<td>" . $row['teachers_fName'] . " " . $row['teachers_lName'] . "</td>";
echo "<td>" . $row['special_fName'] . " " . $row['special_lName'] . "</td>";
echo "<td>" . $row['note'] . "</td>";
echo "<td>" . date("m/d", strtotime($row['started'])) . "</td>";
echo "</tr>";
}
echo "</table>";
?>

All I am trying to do is add a record on a page without the page refreshing.  For that ajax is used.  Here is the code.   It does not add the record to mysql table. Can anyone tell me what I am doing wrong?     record.php

<!DOCTYPE HTML>
<html lang="en"> 
	<head>
		<script type="text/javascript" src="js/jquery-1.11.0.min.js"></script>
		<script type="text/javascript" >
			$(function() {
				$(".submit_button").click(function() {
					var textcontent = $("#content").val();
					var name = $("#name").val();
					var dataString = 'content='+ textcontent + '&name='+name;
					if(textcontent=='')
					{
						alert("Enter some text..");
						$("#content").focus();
					}
					else
					{
						$("#flash").show();
						$("#flash").fadeIn(400).html('<span class="load">Loading..</span>');
						$.ajax({
							type: "POST",
							url: "action.php",
							data: dataString,
							cache: true,
							success: function(html){
								$("#show").after(html);
								document.getElementById('content').value='';
								$("#flash").hide();
								$("#content").focus();
							}  
						});
					}
					return false;
				});
			});
		</script>
	</head>
	<body>
	<?php
		$record_id = $_GET['id']; // getting ID of current page record 
	?>
	
		<form action="" method="post" enctype="multipart/form-data">
			<div class="field">
				<label for="title">Name *</label>
				<input type="text" name="name" id="name" value="" maxlength="20" placeholder="Your name">
			</div>
			<div class="field">
				<label for="content">content *</label>
				<textarea id="content" name="content" maxlength="500" placeholder="Details..."></textarea>
			</div>
			<input type="submit" name="submit" value="submit" class="submit_button">
		</form>
		<div id="flash"></div>
		<div id="show"></div>
		
	</body>
</html>	

action.php

if(isset($_POST['submit'])) {
	if(empty($_POST['name']) || empty($_POST['content'])) {
		
		$error = 'Please fill in the required fields!';
		
	} else {
		try {
			
			$name		= 	trim($_POST['name']);
			$content 	= 	trim($_POST['content']);
					
			$stmt = $db->prepare("INSERT INTO records(record_id, name, content) 
				VALUES(:recordid, :name, :content");
			$stmt->execute(array(
				'recordid'		=> $record_id,
				'name'			=> $name,
				'content' 		=> $content
			));
			
			if(!$stmt){
			
				$error = 'Please fill in the required fields.';
				
			} else {
			
				$success = 'Your post has been submitted.';
			
			}
			
		} catch(Exception $e) {
			die($e->getMessage());
		}
	}
}

Hey guys, I can't wrap my head around how to make this work right...

I have three tables:
Code: [Select]
CREATE TABLE `games` (
  `g_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`g_id`));

CREATE TABLE IF NOT EXISTS `game_player` (
  `r_id` int(11) NOT NULL AUTO_INCREMENT,
  `p_id` int(11) DEFAULT NULL,
  `g_id` int(11) DEFAULT NULL,
  `bool` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`r_id`));

CREATE TABLE IF NOT EXISTS `players` (
  `p_id` int(11) NOT NULL AUTO_INCREMENT,
  `playerid` varchar(150) NOT NULL,
  PRIMARY KEY (`p_id`),
  UNIQUE KEY `playerid` (`playerid`));

The players table is my list of users, and they're tied to the list of games via the game_player table.

So here's my issue... I'm trying to show the full list of games, and then check mark each record where the player does play it. 
This is what I have so far - it shows all the games,  but it's not checking the boxes.

Code: [Select]
$result = mysql_query("SELECT * FROM games") or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$newquery = "SELECT * FROM game_player, players
WHERE game_player.p_id = players.p_id
AND game_player.g_id = ".$row['g_id']. "
AND players.playerid = {$userid}";
$query = mysql_query($newquery) or die(mysql_error());
if($query['bool'] == 1) { $set_checked = " CHECKED";
}
else{ $set_checked = "";
}
echo "<input type=\"checkbox\" name=\"box1\" value=\"".$query['g_id']."\"" . $set_checked . "/>".$row['name']."<br />\n";
}

Hey all!
In the code in question I echo out individual records of data from MySQL successfully. For each record there is a number which is used as a var in the javascript that does the count-down-timer part.
However when I view the resulting page the timer works dynamically only with the first record. With the rest, the timer is static.

Code: [Select]
<?
$result0 = mysql_query("SELECT * FROM table WHERE field='$value'");
while ($riw0 = mysql_fetch_assoc($result0)) {
$seconds1 = $riw0['seconds'] ;
//// echo out data and set variable for the number of seconds to count down
?>

<script language="JavaScript">
var countDownInterval=<?=$seconds1?>;
var c_reloadwidth=200
</script>
<ilayer id="c_reload" width=&{c_reloadwidth}; ><layer id="c_reload2" width=&{c_reloadwidth}; left=0 top=0></layer></ilayer>
<script>
var countDownTime=countDownInterval+1;
function countDown(){
countDownTime--;
if (countDownTime <=0){
countDownTime=countDownInterval;
clearTimeout(counter)
window.location.href="military3.php" //Redirection URL
return
}
var mins = Math.floor(countDownTime/60)
var secs = countDownTime-(mins*60)
if (document.all) //if IE 4+
document.all.countDownText.innerText = mins+" minutes "+secs+ " ";
else if (document.getElementById) //else if NS6+
document.getElementById("countDownText").innerHTML=mins+" minutes "+secs+ " "
else if (document.layers){
document.c_reload.document.c_reload2.document.write('Soldiers will be ready in... <span id="countDownText">'+countDownTime+' </span> seconds')
document.c_reload.document.c_reload2.document.close()
}
counter=setTimeout("countDown()", 1000);
}

function startit(){
if (document.all||document.getElementById)
document.write('Soldiers will be ready in <span id="countDownText">'+countDownTime+' </span> seconds')
countDown()
}

if (document.all||document.getElementById)
startit()
else
window.onload=startit
</script>
<? } ?>

I tried replacing the javascript vars with PHP echoes for unique variables, but then no timer shows up, even static.
So could anyone advice me on how I could use this code to apply for all MySQL records?

Thanks in advance,
Thauwa

P.S. If I am unclear with my quandary, do let me know. Thank you.

Is it no problem to give the SUM of integers in a column, but is it also possible to give the SUM of integers in a record (automatically)?

I'm trying to update records in a Db. I have a record called "classOffering" and I have associated records for that offering called "classSessions". You have 1 classOffering for 1 to 6 classSessions. The process I use has the user create the classOffering and say how many classSession there will be then I create how ever many sessions they say. I then display the classSessions so they can be edited and updated in the Db. I can't seem to update and set more than one record even though there are more.
I know I need to use a while or foreach statement, but I can't seem to do it.
Here's my code so far:

$sessSet = "UPDATE classSessions
                   SET startDate= '$startDate', startTime= '$startTime', time= '$time'
                   WHERE classSessions.sessionId = $sessionId
                   ";


Thanks for any help.

New to php so I'm having trouble with deleting a record from my php table.


This is my code for my page where the records are being displayed. I have a delete statement that is not working properly. Also, I was thinking maybe I need a array to delete records? Please help!





Code: [Select]
<?php
require_once('database.php');
session_start();




if (isset($_POST['add_grade']))
{


$query = "INSERT INTO grades (student_id, grade_type, grade_name, grade_points) ";
$query .= "VALUES (:student_id, :grade_type, :grade_name, :grade_points) ";

$statement = $db->prepare($query);
$statement->bindValue (':student_id', $_SESSION['student_id']);
$statement->bindValue (':grade_type', $_POST['grade_type']);
$statement->bindValue (':grade_name', $_POST['grade_name']);
$statement->bindValue (':grade_points', $_POST['grade_point']);

$statement->execute();

$statement->closeCursor();


if (isset($_POST['remove']))
{

foreach($_POST['delete'] as $delete_id)
{

$query = "DELETE FROM grades WHERE grade_id = $delete_id";
mysqli_query($dbc, $query) or die ('can\'t delete user'); 

}
}


}








?>
<!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>View Course Grades</title>

<link rel="stylesheet" type="text/css" href="main.css" />
</head>

<body>


   



<?php

$student_name = $_SESSION['student_name'];
$student_id = $_SESSION['student_id'];
$query = "SELECT * FROM grades WHERE student_id = :student_id ";

$statement = $db->prepare($query);
$statement->bindValue (':student_id', $student_id);
$statement->execute();
$grades = $statement->fetchAll();
$statement->closeCursor();


echo "<h1>Show Grades for $student_name </h1>";

foreach ($grades as $grade)
{

echo $grade['grade_type'] . " " . $grade['grade_name']. " " . $grade['grade_points'] . "<br />";
}

$query = "select*from se266";  
$result = mysql_query($dsn, $query);  
while($row = mysql_fetch_array($result))
{      
echo '<input type="checkbox" value="' .$row['grade_id'] . '"name="delete[]" />';
echo ' ' .$row['grade_type']; 
                                echo ' ' .$row['grade_name'];
echo '<br />';  

}


?>

<div id="content">
            <!-- display a table of products -->
           
            <table>
                <tr>
                    <th>Grade Type</th>
                    <th>Grade Name</th>
                    <th>Grade Points</th>
                    <th>Remove</th>
                </tr>
                <?php foreach ($grades as $grade) : ?>
                <tr>
                    <td><?php echo $grade['grade_type'];   ?></td>
                    <td><?php echo $grade['grade_name'];   ?></td>
                    <td><?php echo $grade['grade_points']; ?></td>
                    <td><form action="grades.php" method="post">
                       
                        <input type="submit" name="remove" value="Delete" />




                    </form></td>
                </tr>
                <?php endforeach; ?>
            </table>
           
        </div>
    </div>

    <div id="footer">
       
    </div>






<form name="grades" method="post" action="grades.php">

<p>Grade Type<SELECT NAME="grade_type">
<OPTION VALUE="Mid-Term">Mid-Term
<OPTION VALUE="Final">Final
<OPTION VALUE="Lab">Lab
</SELECT>
<br>







Grade Name:<input type="text" name="grade_name" value=""><br />
Grade Points:<input type="text" name="grade_point" value="">

<input type="submit" name="add_grade" value="Add Grade">


</form>

</table>

</body>

</html>