Jump to content


Photo

Anyway round this *SOLVED*


  • Please log in to reply
25 replies to this topic

#1 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 16 September 2006 - 12:10 AM

The code below I think builds an array of rows. It goes on to be used in a dropdown list.

The data of Orderdate comes from a select query to a table. However the table column Orderdate is one of many other columns in the table. It, like other columns gets selected by the user as to what he wants to appear in the list.

2 questions - (1) how do I programatically add in other column names into the line of code below. (2) How do I omit the column Orderdate if its not one of the columns selected by the user?


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


Many thanks


#2 markbett

markbett
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 16 September 2006 - 12:13 AM

http://www.w3schools.../php_arrays.asp

http://www.tizag.com/phpT/arrays.php

first link is probably better....

if you dont want orderdate to appear throw an if in front of it so if"orderdate=="1" then{
run code
}


#3 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 16 September 2006 - 12:28 AM

Thanks, however how can I break up the line below as I have to include or exclude column names. Thanks

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

#4 markbett

markbett
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 16 September 2006 - 12:34 AM

you have arrays within arrays (multi dimen.)  make an if statement to determine which ones get included then append them into the outter array accordingly...

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 16 September 2006 - 01:00 AM

The way to include/exclude column names is in the original SELECT clause of your query

DON'T use SELECT * FROM tablename (unless you really do need all the columns)

ALWAYS specify the columns you want eg
<?php
$sql = "SELECT colname1, colname5 FROM tablename" ;
$res = mysql_query ($sql);
while ($row = mysql_fetch_assoc($res)) {
     $_rows[] = $row;
}
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 16 September 2006 - 01:13 AM

Many thanks. I think its best for me to explain a bit more. I have a page/form which contains checkboxes allowing a user to select columns from a table which will find there way into a dropdown list. So the select query will not contain columns that are not wanted. However, when it gets to the stage of filling the list, which is designed/coded to give column spacing, the arrays error if some table column data does not get to them. What I am trying to do is modify my dropdown list code to be dynamically built before the data flows to it. Hope I am making sense.

One of the lines of code in the listbox creation is:

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

This so far is hard coded, but if a user does not select the Ordernumber column to be in the list, the above codeline is unhappy. Hope this makes it easier to understand where my problems are. Thanks again

#7 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 16 September 2006 - 01:17 AM

Thought it might help to post the code being used for the listbox. As you can see I have had to rem out parts of it to run without error when certain column information was not passed to it.

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);
?>


#8 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 16 September 2006 - 01:18 AM

If you use my code the $_rows array is populated dynamically depending on the selected columns
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#9 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 16 September 2006 - 06:51 AM

Thanks Barand, hope this makes it a bit clearer. The select query is user selected in what columns data is required. Its done before it reaches the code thats giving me grief.

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 == "")
{
// nothing 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.


#10 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 16 September 2006 - 09:49 AM

I still do not understand what should go in the dropdown.

is it

a ) data actually selected by the user

b ) order number and date even if they was not one of the selected fields

Perhaps if you describe what you are trying to do in the application. Posting only code that doesn't do what you want is confusing.

For example, once the user has selected the search criteria and fields to be displayed, are you then trying to give them a means of selecting one of the returned records so you can then display further details of the item?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#11 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 16 September 2006 - 10:55 AM

Thanks Barand. The form/page has checkboxes on that allow certain COLUMNS of a complete order table (at present everything thats in the database) As well as this they have checkboxes which allow for the list to contain 1-6 product groups of all records in the table (this goes into a where statement)

So basically the form allows a dynamic contruction of a select query which then gets relevant data. The problem arises when it gets to the dropdown list, which has non dynamically constructed lines of code. So if something gets ommitted from what it's expecting then it errors.

The page/form gets submitted back to itself, so the first pass there is no data for the dropdown to show anything. The dropdown code provides column spacing so the list looks uniform.

Once I can get passed this problem, I can look at providing the users a chance to select all orders between dates, but until then I am stuck.

Does that help? Thanks

#12 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 16 September 2006 - 11:00 AM

I still do not understand what should go in the dropdown.

is it

a ) data actually selected by the user?

b ) order number and date even if they was not one of the selected fields?

and what do you want to happen when an item is selected from the dropdown?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#13 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 16 September 2006 - 11:15 AM

1. The dropdown list is filled with all rows of a table
2. The dropdown list shows all the user (checkbox panel selected) columns that he wants.

OrderDate  OrderNumber  Description Quantity UnitPrice etc.

3. The dropdown list can show only rows from certain product groups is so wished, again from checkbox selection (selecting 1-6 groups)

The list will show nothing on first page/form load, until checkboxes are checked and the form re submitted back to itself.

4. At this stage I need to do nothing on clicking a list item, its just for viewing.

Thanks for being patient, I know it's a bit of an odd one. Thanks


#14 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 16 September 2006 - 02:20 PM

I know this does not work, as I do not know the correct syntax, but hopefully it will explain what I am trying to do.

My problem line is here when either Orderdate or Ordernumber has not been selected by the user to in the select query (in other words he does not want it included in his dropdown list.

$_rows[] = array("Orderdate"=>mysql_result($result,$i,"Orderdate"), "Ordernumber"=>mysql_result($result,$i,"Ordernumber"), "Value"=>$i);
 
How can I split this up to be in the position of checking each section before finally putting it all into the $_rows[] array?

$tryA=array("Orderdate"=>mysql_result($result,$i,"Orderdate");
  $tryB=array("Ordernumber"=>mysql_result  ($result,$i,"Ordernumber"),"Value"=>$i);

  $_rows[] = array($tryA & "," & $tryB);
 

Just wish this was visual basic, could have walked away from it weeks ago. Thanks again
 

#15 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 16 September 2006 - 02:25 PM

I've just been experimenting with sprintf() formatted lines in a monospace-font select dropdown. Unfortunately the spacing is not preserved as it removes all but a single space between each field value. Just as it would in the html body without pre /pre tags.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#16 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 16 September 2006 - 02:40 PM

Try this
<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">

<?php
    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="test2";                    // SUBSTITUTE YOUR DB NAME  ***********
mysql_connect(localhost);
mysql_select_db($database);

/*
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 Ordernumber as id, $fields FROM ORDERS $where";
    echo "<p>$query</p>";
    $result = mysql_query($query) or die('Problem with query: ' . $query . '
    '. mysql_error());
    $numrows=mysql_num_rows($result);

    //  store returned data
    $data_rows = array();
    while ($row = mysql_fetch_assoc($result)) {
         $data_rows[] = $row;
    }
}


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

?>



<BR>
Include Products<BR>

<?php
    $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 == "")
{
// nothing defined for $fields and $where
}
else
{
    // get data lengths
    $maxlen=array();
    foreach ($data_rows as $row)
        foreach($row as $cname=>$val) 
            $maxlen[$cname] = max($maxlen[$cname], strlen($val));
     

    $contents = "<select style=\"font-family:monospace;\" name=\"selectbox\" multiple size=\"$numrows\">\r\n";

    foreach ($data_rows as $row) {
        $id = $row['id'];
        unset ($row['id']);
        $contents .= "<option value='$id'>";
        $line = '';
        foreach($row as $cname=>$val) {
            $line .= str_pad($val, $maxlen[$cname]+2, chr(160));   //pad with non breaking spaces
        }
        $contents .= $line;
        $contents .= "</option>\r\n";
    }
    $contents .= "</select>";
    ?>
    <p>&nbsp;</p>
    <?php
    echo "<div>Something Here</br>$contents</div>";

}
?>


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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#17 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 16 September 2006 - 02:57 PM

Many thanks for trying, very much appreciated.

#18 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 16 September 2006 - 05:51 PM

Was that how you wanted it?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#19 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 16 September 2006 - 08:29 PM

Sorry, my posting saying thanks very much for trying went out just as you must have been putting your posting up. Absolutely brilliant, works just as I wanted it to!!! I had been away for a while and only just been putting it in. I am so greatful to you, I have spent days/hours trying to get it done so I could move on. You have made my weekend, hope yours is going well, many, many thanks again.

#20 cyprus

cyprus
  • Members
  • PipPipPip
  • Advanced Member
  • 68 posts

Posted 16 September 2006 - 08:57 PM

Slight problem - any ideas. I added a few more column selections for the user, and for some reason the columns start wandering out of line in columns 5,6,7. They follow a column 4 which has varying lengths, despite the fact that column 3 is okay following column 2 which has varying lengths. I tried increasing $maxlen[$cname]+2 to +3 but that just moved things along. Any suggestions, thanks






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users