PHP - Noob : Display Query Results For Error Checking.

I have the following code that searches my database and displays results in a table:

$fields = array("field1", "field2", "field3")
$cols   = implode (', ', $fields); 

$result= mysql_query 
(" SELECT $cols FROM tablename  WHERE ................... ");

if (!$result) {die('Could not search database: ' . mysql_error());}

if($result)
{   
    
    if(mysql_num_rows($result) == 0)
    {
        return "Sorry. No records found in the database";                       
    }
    else 
    {
        $table = "<table border='1' cellpadding='5' cellspacing='5'>";
        while($arr = mysql_fetch_array($result, MYSQL_ASSOC))
        {
            $table .= "\t\t<tr>\n";
            foreach ($arr as $val_col) 
            {
                $table .= "\t\t\t".'<td>'.$val_col.'</td>'."\n";
            }
            $table .= "\t\t</tr>\n";  
        }
        $table .= "</table>";
        echo $table;
    }
    mysql_free_result($result);       
}

As you can see each of the MySQL table fields specified by $fields is displayed in a new column in the html table.
I want to change this so that e.g. "field3" is displayed in a new row instead.

So, instead of the html table looking like:

| "field1-result1" | "field2-result1" | "field3-result1" |
| "field1-result2" | "field2-result2" | "field3-result2" |
| "field1-result3" | "field2-result3" | "field3-result3" |

I want it to look like:

| "field1-result1" | "field2-result1" | 
| "field3-result1" |                          |
| "field1-result2" | "field2-result2" |
| "field3-result2" |                          |
| "field1-result3" | "field2-result3" |
| "field3-result3" |                          |

I guess this is quite straightforward, but I can't work it out! Pls help!

Thanks.

First off, let me say that this will not be a public use site.  We want to create a site that allows us to run queries in our database; this way we don't need to log into the MySQL server when ever we want to run a query.

Basically, I am trying to create something as simple as a form with a text-input and a submit button.  In this text-input, we will write a query (such as SELECT * FROM xxxx).  When we hit submit, we would like to have the MySQL table printed out in a large textbox below.

Our database has multiple tables, so something that works across the entire database seamlessly is key.

Has anyone ever made anything like this?  Is there another way to go about this?  We really just want an easy way to run queries on the go.

Thanks in advance to anyone who replies!

Say a user puts in a support request, and for every request it generates a unqiue string, and enters it into the database.

Ok, now say there is a text field, when the user enters their unique string and it finds a match, it displays the data along with it.

How can I accomplish this? Im kind of new to mysql, but I know basic SQL.

Would be great if somebody could point me in the right direction!

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 don't know precisely how to put it so i will give an example:

suppose you have a search form with input fields for:

-an age range,
-a data range,
-for the sex,
-postal code

as soon as the user would fill in one of these input fields, a query should be generated including a condition doing something with the inputted value.

For example, the user fills in maxAge, the the query should say: SELECT * FROM table WHERE age >= maxAge;

Filling more than one criterion then would do .= " AND criteria (condition) inputvalue " etc...

My question comes to the part about: how to check if there is already a WHERE in the clause and how to put WHERE or AND for every criterion.

Since doing like above, i won't be able to search for another criterion if i don't select maxAge first because then there would be no WHERE in the sql clause.

Hopefully i've put it clear
thanks in advance

Hi there,

I am working on a PHP website. And I am running a query which checks for the expired products.

We check to see if the expiry date is greater than the current systems date then display the record. It looks something like this:

Code: [Select]
select * from products WHERE products.productExpiry) >= '2010-10-13 00:00:00'
'productExpiry' field is of 'Datetime' Datatype
 
And Expiry date is stored in database as:

2010-10-19 04:39:35

Now it works fine. But the problem only arises (no record shows)  when there is no date in the 'productExpiry' field.

How can I check or set a condition in my SQL query that whenever the 'productExpiry' is Null or Blank don't compare it with the current date.

Please reply

All comments and feedbacks are welcomed

Thank you!

Hello,
I have a problem. I run this query:
$query = "SELECT column1 FROM table WHERE column2='$kk'"; $which = $link1; 
mysql_query($query,$which);
some codes....

It is displaying all data correct but  only when $kk exists in column2 and when $kk does not exist, it display blank page.
So, i want to do a check before running above query to make sure that $kk exists in column2. How to check it before running query?

I've decided to rewrite one a couple of my forms, to make them more user friendly.

But I am having problems returning the error. I've created an array ($e) which has the field name ($e['field_name']) for the error, but it isn't displaying any errors.

Here's the code:
case "contact":
$e = array();
if(isset($_POST['sb'])){
$fullname = $sql->real_escape_string($_POST['fullname']);
$email_a = $sql->real_escape_string($_POST['email']);
$email_r = $sql->real_escape_string($_POST['email_r']);
$orderid = $sql->real_escape_string($_POST['orderid']);
$subject = $sql->real_escape_string($_POST['subject']);
$problem = $sql->real_escape_string($_POST['problem']);
# Error Message
function show_error($error,$evar){
$err = ' <span style="color: #ff0000"><strong>'.$error.'</strong></span>';
return $err;
}
# Check for isset
if(!isset($_POST['fullname'])){
$e['fullname'] = show_error('Please enter your full name',$e['fullname']);
}
if(!isset($_POST['email_a'])){
$e['email_a'] = show_error('Please enter your email address.',$e['email_a']);
}
if(!isset($_POST['email_r'])){
$e['email_r'] = show_error('Please re-enter your email address.',$e['email_r']);
}
if(!isset($_POST['subject'])){
$e['subject'] = show_error('Select a subject.',$e['subject']);
}
if(!isset($_POST['problem'])){
$e['problem'] = show_error('Describe your problem.',$e['problem']);
}
if(isset($_POST['email']) AND isset($_POST['email_r']) AND $email_a != $email_r){
$e['email_r'] = show_error('Your email addresses do not match.',$e['email_r']);
}
}
$content = '<h2>
Customer Support - Contact</h2>
<p>
For technical support, sales and product related questions: <a href="'.$tech_support.'" target="_blank">Technical Support</a>.</p>
<p>
For downloading, ordering, refund or other store related questions, please fill out the form below:</p>
<form method="post" name="csupport">
<p>
Please enter your full name:<br />
<input name="fullname" type="text" value="'.$fullname.'" />'.$e['fullname'].'</p>
<p>
Please enter your email address:<br />
<input name="email_a" type="text" value="'.$email_a.'" />'.$e['email_a'].'<br />
Please re-enter your email address:<br />
<input name="email_r" type="text" value="'.$email_r.'" />'.$e['email_r'].'</p>
<p>
Please enter your order number:<br />
<input name="orderid" type="text" value="'.$orderid.'" /></p>
<p>
Please select a subject for your enquiry:<br />
<select name="subject"><option selected="selected" value="">--Select--</option><option value="Billing Question">Billing Question</option><option value="Cancel / Refund Order">Cancel / Refund Order</option><option value="Download Question">Download Question</option><option value="Password Issues">Password Issues</option><option value="Order Question">Order Question</option><option value="Shipping Question">Shipping Question</option><option value="Other">Other</option></select>'.$e['subject'].'</p>
<p>
Describe your problem as best you can:<br />
<textarea cols="45" name="problem" rows="4">'.$problem.'</textarea>'.$e['problem'].'</p>
<p>
<input name="sb" type="hidden" value="sb" /><input name="submit" type="submit" value="Submit" /></p>
</form>
';
$title = 'Contact Support';
break;

Can anyone see what the problem is? Thanks

Hi,

I'm trying to fetch rows of data from a mysqli table and display them in the browser in categories. I have the while loop working fine, but I want to break the results down into categories, instead of one big list sorted by a field using ORDER BY. So for example, let's say I have a database table containing people's names, email address, phone numbers, etc. and I want to display them in the browser in separate tables by the city in which they live (each city name would be a h1 or h2 tag) and the appropriate rows would be displayed under each heading, sorted by the person's name.

I can do it by using multiple while loops, separating each city into it's own array and then repeating my html table code a bunch of times, but I know there has to be a much cleaner way.

Can anyone point me in the right direction?

Thanks!

Hi there.

Im a noob to sql and php not sure if this is right place to post,

Im trying to get a dynamic drop down menu to show the 1st column in my sql database the column is called cat and holds category info ie audio, internet, music ect. ( i have no idea how to do lol )

it has taken me 2 days to find and edit this  the bold and underline'd bit is what im trying to change with the dropdown menu.

Or thinking bout it is there a way to do it with the URL. IE.. page name.php?cat=audio ?
would that be easer ? is there any security issues with doing it that way ?


Code==================================
$db_host = '*******';
$db_user = '******';
$db_pwd = '*****;

$database = 'nbbcj_co_uk';
$table = 'penapps';

if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");

// sending query
$result = mysql_query("SELECT * FROM {$table} WHERE `cat` = 'audio' LIMIT 10 ");
if (!$result) {
    die("Query to show fields from table failed");
}

$fields_num = mysql_num_fields($result);

//echo "<h1>Table: {$table}</h1>";
echo "<table border='1' width='100%'><tr>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    //echo "<td>{$field->name}</td>";
}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
    echo "<tr>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $cell)
        echo "<td>$cell</td>";

    echo "</tr>\n";
}
mysql_free_result($result);
?>
code end ======================

the test page can been seen here http://www.nbbcj.co.uk/testd/1/test1.php

Any more questions let me know

Thanks for any help you can give, a we all have to start some ware lol

thanks kaine.

What would be the correct way to close a mysql query?

At current the second query below returns results from the 1st query AND the 2nd query
The 3rd query returns results from the 1st, 2nd and 3rd query.
 etc etc.

At the moment I get somthing returned along the lines of...

QUERY 1 RESULTS
Accommodation 1
Accommodation 2
Accommodation 3

QUERY 2 RESULTS
Restaurant 1
Restaurant 2
Restaurant 3
Accommodation 1
Accommodation 2
Accommodation 3

QUERY 3 RESULTS
Takeaways 1
Takeaways 2
Takeaways 3
Restaurant 1
Restaurant 2
Restaurant 3
Accommodation 1
Accommodation 2
Accommodation 3

Code: [Select]

<!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" />
<?php 
  include($_SERVER['DOCUMENT_ROOT'].'/include/db.php');
?>
<title>Untitled Document</title>
<style type="text/css">
<!--
-->
</style>
<link href="a.css" rel="stylesheet" type="text/css" />
</head><body>
<div id="listhold">

<!------------------------------------------------------------------------------------------------------------------------------------------------------>

<div class="list"><a href="Placestostay.html">Places To Stay</a><br />
<?php
$title ="TITLE GOES HERE";
$query = mysql_query("SELECT DISTINCT subtype FROM business WHERE type ='Accommodation' AND confirmed ='Yes' ORDER BY name");
echo mysql_error();
while($ntx=mysql_fetch_row($query)) $nt[] = $ntx[0];
$i = -1;
foreach($nt as $value)
{$i++;
$FileName = str_replace(' ','_',$nt[$i]) . ".php";
$FileUsed = str_replace('_',' ',$nt[$i]);
echo "<a href='" . str_replace(' ','_',$nt[$i]) . ".php?title=$title&subtype=$FileUsed'>"  . $nt[$i] . "</a>" . "<br/>";
$FileHandle = fopen($FileName, 'w') or die("cant open file");
$pageContents = file_get_contents("header.php");
fwrite($FileHandle,"$pageContents");}
fclose($FileHandle);
?>
</div>

<!------------------------------------------------------------------------------------------------------------------------------------------------------>

<div class="list"><a href="Eatingout.html">Eating Out</a><br />
<?php
$title ="TITLE GOES HERE";
$query = mysql_query("SELECT DISTINCT subtype FROM business WHERE type ='Restaurant' AND confirmed ='Yes' ORDER BY name");
echo mysql_error();
while($ntx=mysql_fetch_row($query)) $nt[] = $ntx[0];
$i = -1;
foreach($nt as $value)
{$i++;
$FileName = str_replace(' ','_',$nt[$i]) . ".php";
$FileUsed = str_replace('_',' ',$nt[$i]);
echo "<a href='" . str_replace(' ','_',$nt[$i]) . ".php?title=$title&subtype=$FileUsed'>"  . $nt[$i] . "</a>" . "<br/>";
$FileHandle = fopen($FileName, 'w') or die("cant open file");
$pageContents = file_get_contents("header.php");
fwrite($FileHandle,"$pageContents");}
fclose($FileHandle);
?>
</div>

<!------------------------------------------------------------------------------------------------------------------------------------------------------>

<div class="list"><a href="Eatingin.html">Eating In</a><br />
<?php
$title ="TITLE GOES HERE";
$query = mysql_query("SELECT DISTINCT subtype FROM business WHERE type ='Takeaways' AND confirmed ='Yes' ORDER BY name");
echo mysql_error();
while($ntx=mysql_fetch_row($query)) $nt[] = $ntx[0];
$i = -1;
foreach($nt as $value)
{$i++;
$FileName = str_replace(' ','_',$nt[$i]) . ".php";
$FileUsed = str_replace('_',' ',$nt[$i]);
echo "<a href='" . str_replace(' ','_',$nt[$i]) . ".php?title=$title&subtype=$FileUsed'>"  . $nt[$i] . "</a>" . "<br/>";
$FileHandle = fopen($FileName, 'w') or die("cant open file");
$pageContents = file_get_contents("header.php");
fwrite($FileHandle,"$pageContents");}
fclose($FileHandle);
?>
</div>

<!------------------------------------------------------------------------------SKILLED TRADES BELOW--------------------------------------------------->

<div class="list"><a href="Skilledtrades.html">Skilled Trades</a><br/>
<?php
$title ="TITLE GOES HERE";
$query = mysql_query("SELECT DISTINCT subtype FROM business WHERE type ='Skilled Trades' AND confirmed ='Yes' ORDER BY name");
echo mysql_error();
while($ntx=mysql_fetch_row($query)) $nt[] = $ntx[0];
$i = -1;
foreach($nt as $value)
{$i++;
$FileName = str_replace(' ','_',$nt[$i]) . ".php";
$FileUsed = str_replace('_',' ',$nt[$i]);
echo "<a href='" . str_replace(' ','_',$nt[$i]) . ".php?title=$title&subtype=$FileUsed'>"  . $nt[$i] . "</a>" . "<br/>";
$FileHandle = fopen($FileName, 'w') or die("cant open file");
$pageContents = file_get_contents("header.php");
fwrite($FileHandle,"$pageContents");}
fclose($FileHandle);
?>
</div>

can somebody please tel me how to make sure a field is a number?  I have some error checking like


if(empty($postcode))    {        
$errors[] = "Please enter your postcode"; 
}
if(strlen($tel)<11)    {        
$errors[] = "Please enter a phone number that is the correct length";    
} 



But I dont know and cant find how to check for a number only.

My error happens on line #81

This topic has been moved to HTML Help.

http://www.phpfreaks.com/forums/index.php?topic=334287.0