Jump to content

Anyway round this *SOLVED*


cyprus

Recommended Posts

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
Link to comment
Share on other sites

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
[code]<?php
$sql = "SELECT colname1, colname5 FROM tablename" ;
$res = mysql_query ($sql);
while ($row = mysql_fetch_assoc($res)) {
    $_rows[] = $row;
}
?>
[/code]
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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);
?>
Link to comment
Share on other sites

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

[color=blue]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)[/color]
<?
    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')";
    }
--------------------------------------------------

[color=blue]THIS WAS PUT HERE TO CHECK THE QUERY SELECT STATEMENT LOOKED CORRECT.[/color]
    //$sql = "SELECT $fields FROM ORDERS $where";
    //echo "<p>$sql</p>";
}

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

[color=blue]THIS IS JUST FOR THE START OF OPENING THE DB CONNECTION [/color]

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


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

[color=blue]THIS PART IS TO BYPASS OPENING THE DATABASE WITH THE QUERY WHEN THE PAGE IS FIRST OPENED, WITH NO BUILT UP SELECT QUERY[/color]
if($fields == "" || $where == "")
{
// Show the page when nothing is defined for $fields and $where
}
else
{

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

[color=blue]RUN THE QUERY WITH DB[/color]

$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);
}

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

?>

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

<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>";
       
    }

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

[color=blue]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)[/color]
if($fields == "" || $where == "")
{
// nothing defined for $fields and $where
}
else
{
echo '</select name>';

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

  foreach ($_rows as $row):
[color=red]  if (strlen($row['Ordernumber']) > $max['Ordernumber']) {$max['Ordernumber'] = strlen($row['Ordernumber']);}[/color]
 
[color=red]  //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']);}[/color]  $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']}\">";

[color=red]$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']);[/color]

[color=blue]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[/color].


$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.
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.

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

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

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

Just wish this was visual basic, could have walked away from it weeks ago. Thanks again
 
Link to comment
Share on other sites

Try this
[code]
<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>
[/code]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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

Link to comment
Share on other sites

I get this with my test data
[pre]
2006-01-22  1  Widget    25  aaaa          bbbbbb        cccc      dddddddd
2006-02-01  2  Gizmo      15  aaaaaaaaaaa    bb            ccccccccc  dddd 
2006-03-01  3  Wotsit    10  aaaaaaaaaaaaa  bbbbbbbbbbbbb  cccc      dd   
2006-04-01  4  Something  50  aa            bbbb          ccc        d     
[/pre]
Link to comment
Share on other sites

It looks like some of your fields may have extra spaces at the end. cjange the bit that gets the max field lengths to

[code]
    // get data lengths
    $maxlen=array();
    foreach ($data_rows as $k=>$row)
        foreach($row as $cname=>$val) {
            $data_rows[$k][$cname] = trim($val);
            $maxlen[$cname] = max($maxlen[$cname], strlen(trim($val)));
        }
[/code]
Link to comment
Share on other sites

Many thanks. I was thinking there may be an unwanted space somewhere, and whether there was a trim function. I tried your code and it made no difference, however I noticed it to be on one product item where it happens. I went into the database, and found the answer. I had tried adding spaces to the data in the field when I was trying to get columns in line with an ordinary dropdown list code. I removed the bodge that never worked, and hey ho all is well. Everything lines up perfectly. Many thanks again following me through on this nightmare. Best regards
Link to comment
Share on other sites

Just a quick one while this post is still fresh. There are 2 column values, Price and Quantity. Is there an easy way to add to the database data being retrieved for the list, the addition of "£" before the price value/data, and "off" after the quantity values. At the moment all prices look like eg 6.00 and the quanties eg 30.

If its a lot of work then I will give it a miss as you have done enough already. Thanks
Link to comment
Share on other sites

Change that same bit of code again to this
[code]
    // get data lengths
    $maxlen=array();
    foreach ($data_rows as $k=>$row)
        foreach($row as $cname=>$val) {
            $val = trim($val);
            switch ($cname) {
                case 'Price':      $val = '£' . number_format($val,2); break;
                case 'Quantity':    $val = number_format($val, 0) . ' off'; break;
            }
            $data_rows[$k][$cname] = $val;
            $maxlen[$cname] = max($maxlen[$cname], strlen($val));
        }
[/code]

This will (I hope) give
Price = £1,234.56
Qty = 1,000 off
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.