|
PHP - Problem With Mysqli_fetch_array - Not Displaying First Record From Select Query
Similar TutorialsWhat is the best way to display the next record on a web page, from a list of records, without executing the query over and over again each time you display the next record? For example, imagine my user has a list of messages displayed on a web page, and the user filters that list to display all messages with the title "Test", and let's say that returns 12 records. He then clicks on record 1 to read that message. Once he is done reading it, he then wants to read msg 2, and then msg 3 etc. He could hit the Back button in his browser and click on the next record from the list, but it would be more useful to just click on a link that says "Next" which displays the next message in the filtered list of messages (without having to go back to the list). A lot of web sites do this sort of thing, but what is the best way to do it? Do you have to run the original query again each time a new message is loaded, so you know which message is next, or can you somehow store the query to save the server from having to run the same query over and over again? If a user is viewing a list of pictures for example, they could be flicking through the pictures quite quickly, and I would not like to have the server running the same query over and over again every few seconds when it doesn't need to. Advice? (Thanks.) I am learning PHP/MySQL and am modifying a tutorial to make a contact database. All I want is to be able to make a web page for each contact(record) and display one record on each webpage. I want to use the URL featu "http://www.mysite.com/script.php?=item1" type of thing Can any help me as I can't get it to work. I get no errors - it just doesnt display the info. I'm sure I cant be far off as I've only modified a working script ever so slightly. Code: [Select] mysql_connect(localhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); ?> <? $query="SELECT * FROM contacts WHERE id='$id'"; $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); echo "<b><center>Database Output</center></b><br><br>"; ?> <table border="0" cellspacing="2" cellpadding="2"> <tr> <th><font face="Arial, Helvetica, sans-serif">Name</font></th> <th><font face="Arial, Helvetica, sans-serif">Phone</font></th> <th><font face="Arial, Helvetica, sans-serif">Mobile</font></th> <th><font face="Arial, Helvetica, sans-serif">Fax</font></th> <th><font face="Arial, Helvetica, sans-serif">E-mail</font></th> <th><font face="Arial, Helvetica, sans-serif">Website</font></th> </tr> <? $i=0; while ($i < $num) { $first=mysql_result($result,$i,"first"); $last=mysql_result($result,$i,"last"); $phone=mysql_result($result,$i,"phone"); $mobile=mysql_result($result,$i,"mobile"); $fax=mysql_result($result,$i,"fax"); $email=mysql_result($result,$i,"email"); $web=mysql_result($result,$i,"web"); ?> <tr> <td><font face="Arial, Helvetica, sans-serif"><? echo "$first $last"; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo "$phone"; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo "$mobile"; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo "$fax"; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><a href="mailto:<? echo "$email"; ?>">E-mail</a></font></td> <td><font face="Arial, Helvetica, sans-serif"><a href="<? echo "$web"; ?>">Website</a></font></td> </tr> <? ++$i; } echo "</table>"; ?> Below is a page which is supposed to output the name, blog contribution and picture of contributing members of a website. <div id="blog_content" class="" style="height:90%; width:97%; border:5px solid #c0c0c0; background-color: #FFFFFF;"> <!--opens blog content--> <?php //address error handling ini_set ('display_errors', 1); error_reporting (E_ALL & ~E_NOTICE); //include the config file require_once("config.php"); //Define the query. Select all rows from firstname column in members table, title column in blogs table,and entry column in blogs table, sorting in ascneding order by the title entry, knowing that the id column in mebers table is the same as the id column in blogs table. $sql = "SELECT blogs.title,blogs.entry,members.firstname,images.image FROM blogs LEFT JOIN members ON blogs.member_id = members.member_id LEFT JOIN images ON blogs.member_id = images.member_id ORDER BY blogs.title ASC "; $query = mysql_query($sql); if($query !== false && mysql_num_rows($query) > 0) { while(($row = mysql_fetch_assoc($query)) !== false) { echo '<div id="blog_content1" style="float:left; position:relative;bottom:18px;left:13px; background-color: #FFFFFF; height:16.7%; width:100%; border:0px none none;" <!--opens blog_content1 same as main center top 1 and 2 from index page everything scaled down by a factor of 3, heightwise--> <div class="red_bar" style="height:3%; width:100%; border:1px solid #959595;"> <!--a--> <div class="shade1" style="height:5px; width:100%; border:0px none none;"> </div> <div class="shade2" style="height:5px; width:100%; border:0px none none"> </div> <div class="shade3" style="height:5px%; width:100%; border:0px none none"> </div> </div> <!-- closes red bar--> <div class="content" style="height:28.3%; width:100%; border:0px none none;"> <!----> <div class="slideshow" id="keylin" style="float:left; width:20%; border:0px none none;"> <!--a--> <div><img header("Content-type: image/jpeg"); name="" alt="" id="" height="105" width="105" src="$row[image]" /></div> </div> <!-- closes pic--> <div class="content_text" style="float:right; position:relative;top:7px;left:0px; max-height:150px; width:78.5%; border-width:4.5px; border-bottom-style:solid; border-right-style:solid; border-color:#c0c0c0; "> <!--a-->'; echo "<h3>".$row['title']."</h3>"; echo "<p>" .$row['entry']."<br />".$row['firstname']."</p>"; echo '</div> <!-- closes content text--> </div> <!-- closes content--> </div> <!-- closes blog_content1-->'; } } else if($query == false) { echo "<p>Query was not successful because:<strong>".mysql_error()."</strong></p>"; echo "<p>The query being run was \"".$sql."\"</p>"; } else if($query !== false && mysql_num_rows($query) == 0) { echo "<p>The query returned 0 results.</p>"; } mysql_close(); //Close the database connection. ?> </div> <!-- closes blog content--> The select query is designed to retrieve all the blog contributions(represented by the fields blogs.title and blogs.entry) from the database, alongside the contributing member (member.firstname) and the member's picture(images.image), using the member_id column to join the 3 tables involved, and outputs them on the webpage. The title, entry and firstname values are successfully displayed on the resulting page. However, I can't seem to figure out how to get the picture to be displayed. Note that the picture was successfully stored in the database and I was able to view it on a separate page using a simple select query. It is now just a question of how to get it to display on this particularly crowded page. Anyone knows how I can output the picture in the img tag? I tried placing the header("Content-type: image/jpeg"); statement at the top of the php segment, then just right below the select query and finally just right above the img tag, but in every case, I just got a big white blank page starring at me. How and where should I place the header statement? And what else am I to do to get this picture displayed? Any help is appreciated. Hi, my site is displaying something weird, http://86.21.243.231/ on the table at the bottom, it should list the amount of tickets sold. if the user deposits 0.3, then it should display 3 seperate rows for 0.1 with their payout address. but for 0.3, it displays 2 rows of 0.15 instead. the code for the table is: Code: [Select] $sql="SELECT * FROM tickets WHERE `round` = '$round' AND `confirmed` = 'yes' ORDER BY timestamp ASC"; $result=mysql_query($sql); $row=mysql_fetch_array($result); $result=mysql_query($sql); while($row = mysql_fetch_array( $result )) { $ticketamount = $row['amount']; $ticketspurchased = (int)($ticketamount / $ticketprice); $ticketamount = $ticketamount / $ticketspurchased; for ( $counter = 1; $counter <= $ticketspurchased; $counter += 1) { $i++; echo "<tr><td>"; echo $i; echo "</td><td>"; echo $row['address']; echo "</td><td>"; echo $ticketamount; echo "</td><td>"; echo $row['confirmed']; echo "</td><td>"; echo $row['timestamp']; echo "</td></tr>"; } } 0.5 shows correctly Any ideas? Hi, i'm trying to select some entries from my DB, i don't quite understand what's the problem here, i'm using this: Code: [Select] //Customers Online $query = "SELECT COUNT(*) as Anzahl FROM customers WHERE status = 1"; $queryerg = mysql_query($query) OR die(mysql_error()); while($row = mysql_fetch_array($queryerg)){ $customers_online = $row[0]; } Which works fine, now i want to select by country and i'm doing this: Code: [Select] //Customers DE $query = "SELECT COUNT(*) as Anzahl FROM customers WHERE country = 'de'"; $queryerg = mysql_query($query) OR die(mysql_error()); while($row = mysql_fetch_array($queryerg)){ $customers_de = $row[0]; } Which doesn't work? If i print / echo $customers_de i just get a blank value, not even 0 or anything just blank. Any help ? I HOPE THIS IS IN THE CORRECT FORUM! I am developing a program that filters a MySQL database to choose a type of verse. I am using an HTML form with check boxes to select the type. The variable that is passed using $POST is a text string. I am using this string to query the database in the table Events to get all of the verses with that Event_Type. Using mysqli_fetch_array, I am then extacting the ID to use in another MySQL query, this time querying the Verses table to list the verses of this type. What is actually happening is that all the types up to 9 are displaying the correct result, but the ones with ID 10 to 19 are displaying verses with ID = 1 and the last 3 are displaying versed with ID = 2. So it is obvious that it is only looking at the first digit of the ID. How do you get it to recognise complete ID number? PHP Version 5.3.6 Here is the PHP code: Code: [Select] <?php include("Loc_cverse_connect.php"); doDB(); //check for required info from the query string //verify the Event exists $verify_Event_sql = "SELECT ID, Event_Type FROM Events WHERE Event_Type = '".$_POST["Event_Type"]."'"; $verify_Event_res = mysqli_query($mysqli, $verify_Event_sql) or die(mysqli_error($mysqli)); echo $_POST["Event_Type"]; if (mysqli_num_rows($verify_Event_res) < 1) { //this Event does not exist $display_block = "<p><em>You have selected an invalid Event.<br/> Please try again.</em></p>"; } else { //get the Event ID while ($Event_info = mysqli_fetch_array($verify_Event_res)) { $Event_ID = stripslashes($Event_info['ID']); } //gather the Events $get_Event_sql = "SELECT ID, Verse FROM Verses WHERE Event = '".$Event_ID["ID"]."' ORDER BY ID ASC"; $get_Event_res = mysqli_query($mysqli, $get_Event_sql) or die(mysqli_error($mysqli)); //create the display string $display_block = " <p> The Event Type is <b>" .$_POST["Event_Type"]."</b> </p> <table width=\"50%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\" BGCOLOR=\"#87CEEB\" > <tr> <th>ID</th> <th>VERSE</th> </tr>"; while ($Verse_info = mysqli_fetch_array($get_Event_res)) { $Event_id = $Verse_info['ID']; $Verse_text = nl2br(stripslashes($Verse_info['Verse'])); //add to display $display_block .= " <tr> <td width=\"1%\" valign=\"top\">".$Event_id."<br/></td> <td width=\"35%\" valign=\"top\">".$Verse_text."<br/><br/> </tr>"; } //free results mysqli_free_result($get_Event_res); mysqli_free_result($verify_Event_res); //close connection to MySQL mysqli_close($mysqli); //close up the table $display_block .= "</table>"; } ?> <html> <head> <title> List of Verses</title> </head> <body BGCOLOR="#87CEEB"> <h1>Verses</h1> <?php echo $display_block; ?> </body> </html> mysqlnd 5.0.8-dev - 20102224 - $Revision: 308673 $ MySQL Table Structu Event:- ID Event_Type Verses:- ID Event Sub_Type Verse The code below is part of some validation code that I have, it is supposed to count the number of entries in the table 'test_selections' where 'userid' equals the user that is logged in and where the 'transfer_in' date is a date within the current month. I am getting an error saying "Error running query Unknown column '10' in 'where clause' ". Does anybody know why this could be? I am hoping that it is something to do with the snippet of code below so I don't need to post the whole lot!! Any help would be much appreciated. Code: [Select] $current_month = date("m"); $query = "SELECT COUNT(`userid`) as `transfers_count` FROM `test_selections` WHERE `userid` = '{$_SESSION['userid']}' AND Month(`transfer_in`) = `$current_month` GROUP BY `userid`"; Hey everyone I've been working on converting my old mysql code to to the mysqli version and am running into some trouble. I am pulling posts form phpbb and displaying them in a script. My first script below works just fine. Code: [Select] <?php require("connect.php"); $query="SELECT * FROM phpbb_topics WHERE (forum_id='13' OR forum_id='14') AND topic_status = '0'"; $query2="SELECT * FROM phpbb_topics WHERE (forum_id='15' OR forum_id='16') AND topic_status = '0'"; $result=mysql_query($query); $result2=mysql_query($query2); echo "<table border=0 cellpadding=2>"; echo "<tr><td>Lost</td><td>Found</td></tr>"; while($row=(mysql_fetch_array($result)) || $row2=(mysql_fetch_array($result2))) { $forum_id=mysql_result($result,$k,"forum_id"); $topic_id=mysql_result($result,$k,"topic_id"); $topic_title=mysql_result($result,$k,"topic_title"); $forum_id2=mysql_result($result2,$k,"forum_id"); $topic_id2=mysql_result($result2,$k,"topic_id"); $topic_title2=mysql_result($result2,$k,"topic_title"); echo "<tr><td width='15%'><a href=/sahbb/viewtopic.php?f=$forum_id&t=$topic_id>$topic_title</a></td> <td width='15%'><a href=/sahbb/viewtopic.php?f=$forum_id2&t=$topic_id2>$topic_title2</a></td> </tr>"; $k++; } echo "</table>"; mysql_close($connect); ?> however its not very clean or memory efficient so i have converted it to the following. Code: [Select] <?php require("includes_database/mysqli_connect.php"); $query="SELECT * FROM phpbb_topics WHERE (forum_id='13' OR forum_id='14')"; $query2="SELECT * FROM phpbb_topics WHERE (forum_id='15' OR forum_id='16')"; $result=mysqli_query($dbc, $query); $result2=mysqli_query($dbc, $query2); echo "<table border=1 cellpadding=2>"; echo "<tr><td>Lost</td><td>Found</td></tr>"; while(($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) || ($row2 = mysqli_fetch_array($result2, MYSQLI_ASSOC))) { echo "<tr><td width='15%'><a href=/sahbb/viewtopic.php?f=".$row['forum_id']."&t=".$row['topic_id'].">".$row['topic_title']."</a></td> <td width='15%'><a href=/sahbb/viewtopic.php?f=".$row2['forum_id']."&t=".$row2['topic_id'].">".$row2['topic_title']."</a></td> </tr>"; } echo "</table>"; mysqli_free_result($result); mysqli_free_result($result2); mysqli_close($dbc); ?> I know the error is in my while loop but i cant seem to figure out exactly where. The new code displays all of the first query in the table and then below that it displays the next query insted of displaying them side by side below would be an example of what is happening [table Lost Found Lost, Australian Sheppard Lost German Shepherded / Collie Mix Lost Golden Retriever/Cocker Mix Lost Female Grey Tiger Lost Pitbull Mix Lost 2 Chihuahua Lost Black Lab Lost - German Shepard Mix and Lab Mix Lost - Female German Sheperd Aprox 8 Week Old Kitten Found! 2 Pugs FOUND Found 1 Corgi female/ 1 Shih Tzu male Found Tan and Black Australian Shepard Found Australian Shepard Found Intact Male Pitbull/Lab Mix Any help that any of you could provide would be greatly appreciated! I don't know if I'm wording this right. I have a textarea that is entered into a database. When this is edited, I want to store and display a list of all the changes that have been made to it. Like a conversation in a forum. The reason for this is that I want past changes to be locked from editing. Would a new database table storing only this text fields data need to be created? Maybe have column for the work request number and a second column for the text entered? But then wouldn't I need an third identifier column so the changes can be displayed in the right order? Looking for sage like wisdom Thanks Brad_Strickland I have the following code: Code: [Select] $result = mysql_query("SELECT * FROM ESSAY_QUESTIONS WHERE SUBJECT = 'ENGLISH'") or die(mysql_error()); while($essay_data=mysql_fetch_array($result)){ $question = $essay_data['QUESTION']; $id = $essay_data['ID']; echo "<a href=\"englishessays.php?id=$id>"; echo "$question </a>"; echo "<br><br>"; } It seems to be almost working but it just displays one link and the address is all the rest of the code including </a>"; echo "<br><br>"; and other questions. I presume there's an error with how I've written the echo statements so can anyone see it? Or can anyone suggest a better way to do this? On the next page, the php will read the ID from the address and display the information form that record. I making an web app for teachers to assess their students online. The problems I am currently having are on select only one record is displayed and after update no records are displayed in the form even though it was successfully updated.. The program has two files, index.php and process.php. The problems started when got the class name into the query to search and display the class. $result = $mysqli->query("SELECT * FROM data WHERE classroom = '".$classroom."' ORDER BY '.name';") or die($mysqli->error); The query works but doesn't order by name I would like. I would be grateful if anyone could cast an eye over it and give me some tips. Thanks guys. The beers are on me if anyone can sort it, but you'll have to come to Phnom Penh, Cambodia as that is where I live. Anyway here's the code. process.php
<html>
<?php
session_start();
$mysqli = new mysqli("localhost","ray","password","reports") or die(mysqli_error($mysqli));
$id = 0;
$update = false;
$name = '';
$classroom = '';
if (isset($_GET['edit'])){
$id = $_GET['edit'];
$update = true;
$result = $mysqli->query("SELECT * FROM data WHERE id=$id") or die($mysqli->error());
if(isset($result->num_rows) && $result->num_rows > 0) {
$row = $result->fetch_array();
$name = $row['name'];
$classroom = $row['classroom'];
$pacomment = $row['pacomment'];
}
}
if (isset($_POST['update'])){
$id = $_POST['id'];
$pacomment = $_POST['pacomment'];
$mysqli->query("UPDATE data SET pacomment= '$pacomment' WHERE id=$id") or die($mysqli->error);
$_SESSION['message'] = "Record has been updated!";
$_SESSION['msg_type'] = "warning";
header('location: index.php');
}
index,php
<?php require_once 'process.php'; ?>
<!--************************************** Setup Messages **************************************** -->
<?php if (isset($_SESSION['message'])): ?>
<div class="alert alert-<?=$_SESSION['msg_type']?>">
<?php
echo $_SESSION['message'];
unset($_SESSION['message']);
?>
</div>
<?php endif ?>
<!--**************************************End Setup Messages *********************************************** -->
<?php
?>
<!DOCTYPE html>
<html>
<head>
<title>Home of English Reports</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<style>=
body {
margin: 0;
font-family: Arial, Helvetica, sans-serif;
}
.topnav {
overflow: hidden;
background-color: #008080;
}
.topnav a {
float: left;
color: #f2f2f2;
text-align: center;
padding: 14px 16px;
text-decoration: none;
font-size: 17px;
}
.topnav a:hover {
background-color: #ddd;
color: black;
}
.topnav a.active {
background-color: #4CAF50;
color: white;
}
</style>
</head>
<body style="background-color:linen;">
<!--************************************** End Setup Page Styles ************************************* -->
<div class="container">
<form action="index.php" method = get>
<label for="classroom">Class name:</label><br>
<input type="text" id="classroom" name="classroom" value="107i am"><br>
<input type="submit" value="Submit">
</form>
</div>
<?php $resultcomment = $mysqli->query("SELECT EnglishComment FROM comments"); ?>
<!-- ************************************** Begin Connect DB ************************************************ -->
<div class="container" align-content-center>
<?php
$classroom = (isset($_GET['classroom']) ? $_GET['classroom'] : null);
$result = $mysqli->query("SELECT * FROM data WHERE classroom = '".$classroom."' ORDER BY '.name';") or die($mysqli->error);
?>
<!-- ************************************** End Connect DB **************************************************** -->
<div class="row justify-content-center">
<form action="process.php" method="POST">
<input type="hidden" name="id" value="<?php echo $id; ?>">
<div class="form-group">
<h1><label><?php echo $name?></label></h1>
</div>
<form action="process.php" method="POST">
<input type="hidden" name="id" value="<?php echo $id; ?>">
<div class="form-group">
<h3><label>PA Teacher's Comment</label></h3> <select name = "pacomment">
<?php
while($rows = $resultcomment-> fetch_assoc())
{
$EnglishComment = $rows['EnglishComment'];
echo "<option value='$EnglishComment'>$name.$EnglishComment</option>";
}
?></h2>
</select><br>
<p>
<div class="form-group">
<?php
if ($update == true):
?>
<button type="submit" class="btn btn-info" name="update">Update</button>
<?php else: ?>
<!-- <button type="submit" class="btn btn-primary" name="save">Save</button> -->
<?php endif; ?>
</div>
</form>
<!-- ************************************** Begin Setup Table Headers ****************************************** -->
<div class="row justify-content-center">
<table class="table" width = "20%" border = "5" cellpadding = "1";>
<thead>
<tr>
<th><center>Action</center></th>
<th><center>ID</center></th>
<th>Name and Comment</th>
</tr>
</thead>
<!-- ************************************** End Setup Classlist Table Headers ****************************************** -->
<!-- ****** Loop thru Every Record From $result Query Variable and get variables and echo each variable into the table rows ********** -->
<?php
while ($row = $result->fetch_assoc()): ?>
<tr>
<td>
<center><a href="index.php?edit=<?php echo $row['id']; ?>"
class="btn btn-info">Assess</a></center>
</td>
<!-- ************************************** Put data into Classlist table rows ****************************************** -->
<td><center><?php echo $row['studentid']; ?></center></td>
<td><?php echo $row['name']." ".$row['pacomment'] ?></td>
</tr>
<?php endwhile; ?> <!-- ****************** End While() Loop **************************** -->
</table> <!-- *************** End of Classlist Table ****************************************** -->
</div>
</div>
</div>
</body>
</html>
Edited July 25, 2020 by raymon Hi I have a problem that I must admit I have come across before but I can't remember the fix. When I do a simple select query then print it out using a while statement, I can't get it to print out the first record of my results. I am sure this is to do with the execute statement but I can't get it to work properly. I currently have test data in so I know this should bring out 6 records but it only lists 5 This topic has been moved to Application Design. http://www.phpfreaks.com/forums/index.php?topic=347192.0 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());
}
}
}
Ok basic setup is Table name = unit_data I have a field unit_paid_date colum type = DATE unit_paid_date = 2011-03-02 thats yyyy-mm-dd lets say for example the DueDate is the Second of every month I need to select all records from unit_data that are PAST DUE so if the last paid date is 2011-01-02 that record will pop up as PAST DUE BUT if that paid date is say 2011-03-10 it will not be shown because the invoice was paid Ahead of due date I had this almost working properly -- but can use any help you guys offer. I was attempting to use mysql WHERE queries and do checks against paid_date but failed Thanks Hello all,
Based on the suggestion of you wonderful folks here, I went away for a few days (to learn about PDO and Prepared Statements) in order to replace the MySQLi commands in my code. That's gone pretty well thus far...with me having learnt and successfully replaced most of my "bad" code with elegant, SQL-Injection-proof code (or so I hope).
The one-and-only problem I'm having (for now at least) is that I'm having trouble understanding how to execute an UPDATE query within the resultset of a SELECT query (using PDO and prepared statements, of course).
Let me explain (my scenario), and since a picture speaks a thousand words I've also inlcuded a screenshot to show you guys my setup:
In my table I have two columns (which are essentially flags i.e. Y/N), one for "items alreay purchased" and the other for "items to be purchased later". The first flag, if/when set ON (Y) will highlight row(s) in red...and the second flag will highlight row(s) in blue (when set ON).
I initially had four buttons, two each for setting the flags/columns to "Y", and another two to reverse the columns/flags to "N". That was when I had my delete functionality as a separate operation on a separate tab/list item, and that was fine.
Now that I've realized I can include both operations (update and delete) on just the one tab, I've also figured it would be better to pare down those four buttons (into just two), and set them up as a toggle feature i.e. if the value is currently "Y" then the button will set it to "N", and vice versa.
So, looking at my attached picture, if a person selects (using the checkboxes) the first four rows and clicks the first button (labeled "Toggle selected items as Purchased/Not Purchased") then the following must happen:
1. The purchased_flag for rows # 2 and 4 must be switched OFF (set to N)...so they will no longer be highlighted in red.
2. The purchased_flag for row # 3 must be switched ON (set to Y)...so that row will now be highlighted in red.
3. Nothing must be done to rows # 1 and 5 since: a) row 5 was not selected/checked to begin with, and b) row # 1 has its purchase_later_flag set ON (to Y), so it must be skipped over.
Looking at my code below, I'm guessing (and here's where I need the help) that there's something wrong in the code within the section that says "/*** loop through the results/collection of checked items ***/". I've probably made it more complex than it should be, and that's due to the fact that I have no idea what I'm doing (or rather, how I should be doing it), and this has driven me insane for the last 2 days...which prompted me to "throw in the towel" and seek the help of you very helpful and intellegent folks. BTW, I am a newbie at this, so if I could be provided the exact code, that would be most wonderful, and much highly appreciated.
Thanks to you folks, I'm feeling real good (with a great sense of achievement) after having come here and got the great advice to learn PDO and prepared statements.
Just this one nasty little hurdle is stopping me from getting to "end-of-job" on my very first WebApp. BTW, sorry about the long post...this is the best/only way I could clearly explaing my situation.
Cheers guys!
case "update-delete":
if(isset($_POST['highlight-purchased']))
{
// ****** Setup customized query to obtain only items that are checked ******
$sql = "SELECT * FROM shoplist WHERE";
for($i=0; $i < count($_POST['checkboxes']); $i++)
{
$sql=$sql . " idnumber=" . $_POST['checkboxes'][$i] . " or";
}
$sql= rtrim($sql, "or");
$statement = $conn->prepare($sql);
$statement->execute();
// *** fetch results for all checked items (1st query) *** //
$result = $statement->fetchAll();
$statement->closeCursor();
// Setup query that will change the purchased flag to "N", if it's currently set to "Y"
$sqlSetToN = "UPDATE shoplist SET purchased = 'N' WHERE purchased = 'Y'";
// Setup query that will change the purchased flag to "Y", if it's currently set to "N", "", or NULL
$sqlSetToY = "UPDATE shoplist SET purchased = 'Y' WHERE purchased = 'N' OR purchased = '' OR purchased IS NULL";
$statementSetToN = $conn->prepare($sqlSetToN);
$statementSetToY = $conn->prepare($sqlSetToY);
/*** loop through the results/collection of checked items ***/
foreach($result as $row)
{
if ($row["purchased"] != "Y")
{
// *** fetch one row at a time pertaining to the 2nd query *** //
$resultSetToY = $statementSetToY->fetch();
foreach($resultSetToY as $row)
{
$statementSetToY->execute();
}
}
else
{
// *** fetch one row at a time pertaining to the 2nd query *** //
$resultSetToN = $statementSetToN->fetch();
foreach($resultSetToN as $row)
{
$statementSetToN->execute();
}
}
}
break;
}
CRUD Queston.png 20.68KB
0 downloads
I create list box which value contains from the table of database but now i want to display the data related to the value of list box. please any body can help me to solve the problem My table contains id, category_name, title, discription and list box contain the value from the filed category_name of the table now i want to display title and discription according to the category_name. please send me code i am totally confused.. hi i am in situation to display input box along with select option , here is my code <?php $run= $dbc->db("SELECT * FROM `table` WHERE `id`='$id'"); ?> <select name="service"> <?php while ($result = mysql_fetch_array($run)) { ?> <option value="<?php echo $result['id'] ; ?>"><?php echo $result['service'] ; ?></option> <?php // This is problem i need to get this as hidden field but it breaks my select options. ?> <input type="hidden" value="<?php echo $result['data'] ; ?>" name="data" /> <?php } ?> </select> so here i am stuck <input type="hidden" value="<?php echo $result['id'] ; ?>" name="data" /> when i repeat in while loop it breaks options box and display other options without in select box. any help please thanks hirealimo.com.au/code1.php this works as i want it: Quote SELECT * FROM price INNER JOIN vehicle USING (vehicleID) WHERE vehicle.passengers >= 1 AND price.townID = 1 AND price.eventID = 1 but apparelty selecting * is not a good thing???? but if I do this: Quote SELECT priceID, price FROM price INNER JOIN vehicle....etc it works but i lose the info from the vehicle table. but how do i make this work: Quote SELECT priceID, price, type, description, passengers FROM price INNER JOIN vehicle....etc so that i am specifiying which colums from which tables to query?? thanks I have this code below: Code: [Select] members_count <= ".$diff." || members_count >= ".$defmemcount." My test says $diff = 6 and $defmemcount = 11, members_count = 14 So how come this returns as if members_count isn't less or equal to 6 OR higher or equal to 11? |
|||||||