Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Building statements

Status
Not open for further replies.
ZOR

ZOR

Technical User
Jan 30, 2002
2,963
GB
The code parts below are working, however I cannot find how to remedy the situation when my database select query from checkboxes (ie user selects what table columns are wanted in list)when items are not called for ie may not want Ordernumber. As it is the code below expects it.

How do I dynamically rebuild the array?
$_rows[] = array("Orderdate"=>mysql_result($result,$i,"Orderdate"), "Ordernumber"=>mysql_result($result,$i,"Ordernumber"), "Value"=>$i);

What can I base my If else on to remedy this?
foreach ($_rows as $row):
if (strlen($row['Ordernumber']) > $max['Ordernumber']) {$max['Ordernumber'] = strlen($row['Ordernumber']);}
if (strlen($row['Orderdate']) > $max['Orderdate']) {$max['Orderdate'] = strlen($row['Orderdate']);}
//if (strlen($row['Caption']) > $max['Caption']) {$max['Caption'] = strlen($row['Caption']);}
//if (strlen($row['Description']) > $max['Description']) {$max['Description'] = strlen($row['Description']);}
$rows[] = $row;
endforeach;

Again, what can I refer to in my if else?
$contents .= sprintf("%-'#".($max['Ordernumber'] + 2)."s", $row['Ordernumber']);
$contents .= sprintf("%-'#". ($max['Orderdate'] + 2)."s", $row['Orderdate']);
//$contents .= sprintf("%-'#". ($max['Caption'] + 4)."s", $row['Caption']);
//$contents .= sprintf("%-'#". ($max['Description'] + 2)."s", $row['Description']);

Many thanks
 
Sort by date Sort by votes
Can you give a more explicit explanation of what you're trying to do here? I understand that you are trying to use use input to determine what columns from a database to display, but your code snippets are not clear on what you're doing.



Want the best answers? Ask the best questions! TANSTAAFL!
 
Upvote 0 Downvote
Thanks. I am trying to bypass parts of the code should the items not be selected by the user. The code is to fill a dropdown list. ie, user may not want Ordernumber or Orderdate to be included.

<?
session_start();
?>

<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Date</title>
</head>
View Archive Orders
<body bgcolor="#C0C0C0">

<?
if (isset($_POST['submit'])) {
//$fields = 'op1'; // default
$where = '';
if (count($_POST['showfield']))
$fields = join(',', $_POST['showfield']);

if (count($_POST['product'])) {
$prodlist = join("','", $_POST['product']);
$where = "WHERE GGroup IN ('$prodlist')";
}
//$sql = "SELECT $fields FROM ORDERS $where";
//echo "<p>$sql</p>";
}

$username="root";
// $password="password";
$database="";
mysql_connect(localhost,$username);

if($fields == "" || $where == "")
{
// Show the page when nothing is defined for $fields and $where
}
else
{
$query = "SELECT $fields FROM ORDERS $where";
echo "<p>$query</p>";
@mysql_select_db($database) or die( "Unable to select database");
$result = mysql_query($query) or die('Problem with query: ' . $query . '
'. mysql_error());
$numrows=mysql_numrows($result);
}

?>

<FORM method='post'>
Include Data<br>

<?
$showfields = array (
Orderdate => 'Order Date',
Ordernumber => 'Order Number',
Description => 'Caption',
Duration => 'Duration',
);
foreach ($showfields as $id =>$prod) {
if ($_POST['showfield']) {
// was value of id in those posted?
$chk = in_array($id, $_POST['showfield']) ? 'checked' : '';
}
else $chk = '';
echo "<input type='checkbox' name='showfield[]' value='$id' $chk>$prod<br>";

}

?>

<br>

Include Products<br>
<?
$products = array (
1 => 'Digital Betacam',
2 => 'Betacam SP',
3 => 'DVCPro',
4 => 'HDCAM',
5 => 'Mini DV'
);
foreach ($products as $id =>$prod) {
if ($_POST['product']) {
// was value of id in those posted?
$chk = in_array($id, $_POST['product']) ? 'checked' : '';
}
else $chk = '';
echo "<input type='checkbox' name='product[]' value='$id' $chk>$prod<br>";

}

if($fields == "" || $where == "")
{
// Show the page when nothing is defined for $fields and $where
}
else
{
echo '</select name>';

for ($i=0; $i<=$numrows-1; $i++):
//print $numrows;
$_rows[] = array("Orderdate"=>mysql_result($result,$i,"Orderdate"), "Ordernumber"=>mysql_result($result,$i,"Ordernumber"), "Value"=>$i);
endfor;

foreach ($_rows as $row):
if (strlen($row['Ordernumber']) > $max['Ordernumber']) {$max['Ordernumber'] = strlen($row['Ordernumber']);}

//if (strlen($row['Orderdate']) > $max['Orderdate']) {$max['Orderdate'] = strlen($row['Orderdate']);}
//if (strlen($row['Caption']) > $max['Caption']) {$max['Caption'] = strlen($row['Caption']);}
//if (strlen($row['Description']) > $max['Description']) {$max['Description'] = strlen($row['Description']);}
$rows[] = $row;
endforeach;

$contents = "<select style=\"font-family:monospace;\" name=\"selectbox\" multiple size=\"$numrows\">\r\n";
foreach ($rows as $row):
$contents .= "<option value=\"{$row['Value']}\">";

$contents .= sprintf("%-'#".($max['Ordernumber'] + 2)."s", $row['Ordernumber']);
//$contents .= sprintf("%-'#". ($max['Orderdate'] + 2)."s", $row['Orderdate']);
//$contents .= sprintf("%-'#". ($max['Caption'] + 4)."s", $row['Caption']);
//$contents .= sprintf("%-'#". ($max['Description'] + 2)."s", $row['Description']);

$contents .= "</option>\r\n";
endforeach;
$contents .= "</select>";
$contents = str_replace("#", "&nbsp;", $contents);
?>
<p>&nbsp;</p>
<?
echo "<div>Something Here</br>$contents</div>";

}
?>


<input type="submit" name="submit" value="Submit">
</FORM>
 
Upvote 0 Downvote
I would like at this juncture to make a couple of constructive cricitisms of your coding style. I strongly recommend the following:[ul][li]Pick exactly one style of control structure and use it religiously. In some places you use curly braces ("{....}") to delineate blocks of code, in others, you use the alternative ":...end{if|for|while}" syntax. Having to watch for both makes your code less readable.[/li][li]Indent. Indent, indent, indent. Pick an indentation style (link, I use the Allman style, but that's just personal preference) and use it religiously. Having to be aware of multiple styles of indentation in one file makes the reader work harder than necessary.[/li][/ul]I further recommend less strongly:[ul][li]When you have an if-else statement that only requires blocking around one clause:

[tt]if ($something == 'some value')
{
do_this();
do_that();
}
else
do_the_other();[/tt]

or

[tt]
if ($something == 'some value')
do_this();
else
{
do_that();
do_the_other();
}[/tt]

Use the curly braces around both, even if not necessary:

[tt]if ($something == 'some value')
{
do_this();
do_that();
}
else
{
do_the_other();
}[/tt]

I have found this to improve readability because it standardizes your code.[/li][ul]



Want the best answers? Ask the best questions! TANSTAAFL!
 
Upvote 0 Downvote
Sorry, I didn't mean to hit "submit" when I did.

I'm still not sure what you intend this code to do. Can you give me a 10,0000-foot view of the program flow?



Want the best answers? Ask the best questions! TANSTAAFL!
 
Upvote 0 Downvote
10,0000-foot view? Thanks
 
Upvote 0 Downvote
Are you going to help me to help you? Or are you just going to act as my spell-checker?

Again, tell me in some detail what you intend this code to do.

This script produces an HTML page based on some input. What is the source of this input: does this script produce a form that submits back to this same script? Is there some static HTML page that starts the process? Is there another script that produces the input form?

The script uses than input to select certain columns from a table and certain rows based on a optional search query. However, nowhere in the script is there an invocation of any mysql_fetch_[assoc|array|object]() function which would actually fetch the results of that query. What do you do with that query once you've built it?



Want the best answers? Ask the best questions! TANSTAAFL!
 
Upvote 0 Downvote
Sorry Sleipnir, a lot going on over here, misread into you last posting.

The form/page is very basic at present, just getting the basics together. It.s a form that submits back to itself only to display a dropdown list of users selection of what table columns are displayed, and what product groups of 6 are held in the table. Its basically to display past customer orders. One day I will learn how to use the code display methods of this forum.


<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Date</title>
</head>
View Archive Orders
<body bgcolor="#C0C0C0">

THIS BIT LOOKS AT WHAT GETS POSTED BACK AFTER SUBMISSION FROM THE USER CHECKBOXES BEING CHECKED. FROM THESE VALUES THE SELECT STATEMENT IS CONSTRUCTED (IE THE SELECT XXXXX AND THE WHERE XXXX)

<?
if (isset($_POST['submit'])) {
//$fields = 'op1'; // default
$where = '';
if (count($_POST['showfield']))
$fields = join(',', $_POST['showfield']);

if (count($_POST['product'])) {
$prodlist = join("','", $_POST['product']);
$where = "WHERE GGroup IN ('$prodlist')";
}
--------------------------------------------------

THIS WAS PUT HERE TO CHECK THE QUERY SELECT STATEMENT LOOKED CORRECT.

//$sql = "SELECT $fields FROM ORDERS $where";
//echo "<p>$sql</p>";
}

--------------------------------------------------

THIS IS JUST FOR THE START OF OPENING THE DB CONNECTION

$username="root";
// $password="password";
$database="";
mysql_connect(localhost,$username);


--------------------------------------------------

THIS PART IS TO BYPASS OPENING THE DATABASE WITH THE QUERY WHEN THE PAGE IS FIRST OPENED, WITH NO BUILT UP SELECT QUERY

if($fields == "" || $where == "")
{
// Show the page when nothing is defined for $fields and $where
}
else
{

--------------------------------------------------

RUN THE QUERY WITH DB

$query = "SELECT $fields FROM ORDERS $where";
echo "<p>$query</p>";
@mysql_select_db($database) or die( "Unable to select database");
$result = mysql_query($query) or die('Problem with query: ' . $query . '
'. mysql_error());
$numrows=mysql_numrows($result);
}

--------------------------------------------------

?>

THIS PART PUTS CHECKBOXES ON HTML PAGE, PLUS TEXT, AND REINSTATES THE STATUS OF THE CHECKBOXES (IE KEEPS THEM CHECKED OR UNCHECKED AFTER SUBMITTING)


<FORM method='post'>
Include Data<br>

<?
$showfields = array (
Orderdate => 'Order Date',
Ordernumber => 'Order Number',
Description => 'Caption',
Duration => 'Duration',
);
foreach ($showfields as $id =>$prod) {
if ($_POST['showfield']) {
// was value of id in those posted?
$chk = in_array($id, $_POST['showfield']) ? 'checked' : '';
}
else $chk = '';
echo "<input type='checkbox' name='showfield[]' value='$id' $chk>$prod<br>";

}

?>

<br>

Include Products<br>
<?
$products = array (
1 => 'Digital Betacam',
2 => 'Betacam SP',
3 => 'DVCPro',
4 => 'HDCAM',
5 => 'Mini DV'
);
foreach ($products as $id =>$prod) {
if ($_POST['product']) {
// was value of id in those posted?
$chk = in_array($id, $_POST['product']) ? 'checked' : '';
}
else $chk = '';
echo "<input type='checkbox' name='product[]' value='$id' $chk>$prod<br>";

}

--------------------------------------------------

THIS PART CREATES THE DROPDOWN LIST. EARLY PART OF THE CODE PREVENTS LIST BEING DONE IF THERE IS AN INCOMPLETE SELECT QUERY, DATABASE HAS BEEN BYPASSED, NO DATA YET FOR LIST (IE PAGE FIRST LOAD)

if($fields == "" || $where == "")
{
// Show the page when nothing is defined for $fields and $where
}
else
{
echo '</select name>';

for ($i=0; $i<=$numrows-1; $i++):
$_rows[] = array("Orderdate"=>mysql_result($result,$i,"Orderdate"), "Ordernumber"=>mysql_result($result,$i,"Ordernumber"), "Value"=>$i);
endfor;

foreach ($_rows as $row):
if (strlen($row['Ordernumber']) > $max['Ordernumber']) {$max['Ordernumber'] = strlen($row['Ordernumber']);}

//if (strlen($row['Orderdate']) > $max['Orderdate']) {$max['Orderdate'] = strlen($row['Orderdate']);}
//if (strlen($row['Caption']) > $max['Caption']) {$max['Caption'] = strlen($row['Caption']);}
//if (strlen($row['Description']) > $max['Description']) {$max['Description'] = strlen($row['Description']);}
$rows[] = $row;
endforeach;

$contents = "<select style=\"font-family:monospace;\" name=\"selectbox\" multiple size=\"$numrows\">\r\n";
foreach ($rows as $row):
$contents .= "<option value=\"{$row['Value']}\">";

$contents .= sprintf("%-'#".($max['Ordernumber'] + 2)."s", $row['Ordernumber']);
//$contents .= sprintf("%-'#". ($max['Orderdate'] + 2)."s", $row['Orderdate']);
//$contents .= sprintf("%-'#". ($max['Caption'] + 4)."s", $row['Caption']);
//$contents .= sprintf("%-'#". ($max['Description'] + 2)."s", $row['Description']);


THE STATEMENTS/CODE ABOVE ALL WORKS IF ORDERNUMBER/ORDERDATE/CAPTION/DESCRIPTION WERE SELECTED BY THE USER. HOWEVER IF THEY WERE NOT, THEN UNDERSTANDABLY THE CODE ERRORS.




$contents .= "</option>\r\n";
endforeach;
$contents .= "</select>";
$contents = str_replace("#", "&nbsp;", $contents);
?>
<p>&nbsp;</p>
<?
echo "<div>Something Here</br>$contents</div>";

}
?>


<input type="submit" name="submit" value="Submit">
</FORM>


I don't know how ligible this will all be until I hit the button, only hope it makes it a bit clearer, thanks.

 
Upvote 0 Downvote
One day I will learn how to use the code display methods of this forum.
Click to expand...
My criticisms earlier have nothing whatsoever to do with the standard of this forum. My earlier criticisms have to do with good coding practice in general.

I cannot recommend strongly enough that you make developing a solid coding style a priority, as it will become more and more critical as you develop more complex programs. And there is never a more appropriate time than the present to build a good habit.


Now I think I understand your code. What behavior is you want to change?



Want the best answers? Ask the best questions! TANSTAAFL!
 
Upvote 0 Downvote
Sorry, but what I was meaning was I don't know how to break up my code and present it in blocks as you have done in your last message, the Quote: in a bordered rectangle.

I quite understand your comment on tidy code, but my code is put together roughly just to test out.

Re my problems, I need to find a way round certain bits of code which are hard coded where data may get ommited due to not being checkmarked. The line below might not get Ordernumber data as the user does not want it in the list

$_rows[] = array("Orderdate"=>mysql_result($result,$i,"Orderdate"), "Ordernumber"=>mysql_result($result,$i,"Ordernumber"), "Value"=>$i);

The same goes for the lines remmed out in:

//if (strlen($row['Orderdate']) > $max['Orderdate']) {$max['Orderdate'] = strlen($row['Orderdate']);}

Also in:

//$contents .= sprintf("%-'#". ($max['Orderdate'] + 2)."s", $row['Orderdate']);

Regards
 
Upvote 0 Downvote
Sorry.....that would be surrounding your code with [ignore]
Code: 
...
[/ignore] tags. I will leave as an exercise for the student as to how I got it to output those tags rather than using them as formatting commands, hee hee.....


I'm not sure I understand where your problem starts, but perhaps posting similar code will help.

Assuming the following MySQL table "names":
[tt]+----+-----------+----------+--------------+
| Id | firstname | lastname | phone |
+----+-----------+----------+--------------+
| 1 | Adam | Aames | 123-456-7890 |
| 2 | Bert | Benning | 234-567-8901 |
| 3 | Charles | Cross | 345-678-9012 |
| 4 | Daniel | Dempsey | 456-789-0123 |
| 5 | Earl | Evans | 567-890-1234 |
+----+-----------+----------+--------------+[/tt]

A script that does similar things to what you describe might look something like:

Code: 
<?php

//this function displays the form which the user fills in
//to request specific data to be displayed.  If this form
//has already been submitted to this script, then this function
//will maintain the state of fields

//There are no hard-coded table column names.  This function fetches
//the table's column names from the table and uses those.

function display_form ()
{
	global $dbh;
	
	print '<form method="post" action="' . $_SERVER['PHP_SELF'] . '">';
	print '<table border="1">' . "\r\n";
	
	$query = "show columns from names where field != 'Id'";
	$rh = mysql_query($query, $dbh);
	$num_cols = mysql_num_rows($rh);
	print '<tr><td colspan="' . $num_cols . '">Search:' . "\r\n";
	print '<tr>';
	print 
	
	$checkbox = '';
	while ($row = mysql_fetch_assoc($rh))
	{
		print '<td><input type="radio" name="search_field" value="' . $row['Field'] . '"';
		if (isset ($_POST['search_field']))
		{
			if ($_POST['search_field'] == $row['Field'])
			{
				print ' checked';
			}
		}
		print '>' . $row['Field'] . "\r\n";
	
		$checkbox .= '<td><input type="checkbox" name="fields[' . $row['Field'] . ']"';
		if (isset ($_POST['fields'][$row['Field']]))
		{
			$checkbox .= ' checked';
		}
		$checkbox .= '>' . $row['Field'] . "\r\n";
	}
	
	print '<tr><td colspan="' . $num_cols . '">For the text:<input type="text" name="search_text" value="';
	if (isset($_POST['search_text']))
	{
		print $_POST['search_text'];
	}
	
	print '">';
	
	print '<tr><td colspan="' . $num_cols . '">Display the columns:';
	print '<tr>';
	print $checkbox;
	
	print '<tr><td colspan=' . $num_cols . '><input type="submit">';
	print '</table>';
	print '</form>';
}


//This function fetches and displays results of the user's query
function display_results ()
{
	global $dbh;

	if (isset($_POST['fields']) && count($_POST['fields']) != 0)
	{
		$select_fields = join(',', array_keys($_POST['fields']));
		
		$where_clause = '';
		if (isset($_POST['search_field']) && $_POST['search_text'] != '')
		{
			$where_clause = " WHERE " .$_POST['search_field'] . " LIKE('%" . $_POST['search_text'] . "%')";
		}
		
		$query = "SELECT " . $select_fields . " FROM names" . $where_clause;
		
		$rh = mysql_query($query, $dbh);
		
		if (mysql_num_rows($rh) == 0)
		{
			print 'No matching rows';
		}
		else
		{
			print '<table border="1">';
			print '<tr>';
			foreach (array_keys($_POST['fields']) as $field_names)
			{
				print '<td>' . $field_names;
			}
			
			while ($row = mysql_fetch_array($rh, MYSQL_NUM))
			{
				print '<tr>';
				foreach ($row as $column)
				{
					print '<td>' . $column;
				}
			}
			print '</table>';
		}
	}
	else
	{
		print 'No display fields selected';
	}
}

//script execution starts here

print '<html><body>';

$dbh = mysql_connect ('localhost', 'test', 'test');
if ($dbh !== FALSE)
{
	$result = mysql_select_db ('test', $dbh);
	
	if ($result !== FALSE)
	{
		display_form();

		if (isset($_POST['search_text']))
		{
			display_results();
		}
	}
	else
	{
		print 'Error selecting database';
	}
}
else
{
	print 'Error connection to database';
}

print '</body></html>';
?>

Perhaps this can give you some ideas.



Want the best answers? Ask the best questions! TANSTAAFL!
 
Upvote 0 Downvote
Thankyou for the script. I will give it a try, however if I can find a way to dynamically modify the line below, the rest I should be able to overcome with If statements.

The code line below could contain any permutation of 6 tablenames following whatever the user selected in or out.

Code: 
$_rows[] = array("Orderdate"=>mysql_result($result,$i,"Orderdate"),"Value"=>$i);

OR THIS

Code: 
$_rows[] = array("Orderdate"=>mysql_result($result,$i,"Orderdate"), "Ordernumber"=>mysql_result($result,$i,"Ordernumber"), "Value"=>$i);
 
Upvote 0 Downvote
The 6 column names have not been hard coded in yet re the problems. 6 columns would be:

Date,Ordernumber,Description,Duration,Quantity,Pricetotal.

Thanks
 
Upvote 0 Downvote
Well, if your database is that fluid, you can get the column names from the database too...

mysql_fieldname($result, $colnum);

It can also be done with a query, which is prefered practice, but I don't see that on a quick glance through my mySQL book.

To take a step back, what if you always ask for all columns, always split all columns into your array, and CONDITIONALLY display them? Might make your logic a lot easier...



 
Upvote 0 Downvote
Thanks Miros. Can I do something like this?. I have no clue as to the correct syntax but it should be clear what I mean.

IF ORDERDATE HAS BEEN SELECTED FOR THE QUERY, THEN:
$tryA=array("Orderdate"=>mysql_result$result,$i,"Orderdate");

IF ORDERNUMBER HAS BEEN SELECTED FOR THE QUERY, THEN
$tryB=array ("Ordernumber"=>mysql_result$result,$i,"Ordernumber"),"Value"=>$i);

ADD ANY FUTHER COLUMNS IN HERE WHICH ARE IN THE USER SELECTION CHECKBOXES

THEN ADD WHATS THERE INTO THE STATEMENT:
$_rows[] = array($tryA & "," & $tryB);

Does that make sense?

 
Upvote 0 Downvote
I'm not sure if that's how you should build your final array, but yes, that's about what I meant.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobMCT
  • Locked
  • Question
Old but new again - Debugging PDO prepared statements
Replies
1
Views
142
vacunita
vacunita
emozley
  • Locked
  • Question
Building a dynamic SQL statement based on contents of an array
Replies
3
Views
138
ChrisHirst
ChrisHirst
d0nny
  • Locked
  • Question
Building an XML file.
Replies
2
Views
41
jpadie
jpadie
southbeach
  • Locked
  • Question
Building an multi-level associative array from flat file
Replies
2
Views
37
jpadie
jpadie
EagleM
  • Locked
  • Question
Building a query based on querystring array passed by $_GET
Replies
13
Views
68
jpadie
jpadie

Part and Inventory Search

Sponsor

Back
Top