substr from query

apek · January 7th, 2004, 01:24 AM

hello all gurus...
i have a problem with the substr function..
firstly i wanna tell that i'm a newbie in php..

now i describe my problem...
i have a combobox whick the lists i load up from mysql database...

the list contains ID such as B01,E01 and D01...
i call all these ID's Major ID...

There are another ID's such as B02,B03,E02,E03,D02 and D03 which i call Minor ID...

all wanna do is when a user select the major ID from the first combobox(Major ID) then PHP automatically recognize the first letter (let say the first letter from B01 is B) and then load up the the second combobox (Minor ID) with ID that only started with B....(do u understand?i'm sorry..my english is pretty bad...

for reference,i enclose here my silly code snippets:

Code:


          <select name="noTag" size="1">

            <option value="0">Choose Major ID   </option>

            <?php



   //----------------------------------------------------

   //          Access Data From MySQL

   //          TABLE : tPartMajor

   //----------------------------------------------------



    $query2 = "Select * from ".$DBprefix."tPartMajor";

    $mesin = mysql_query($query2);

            while ($dbq = mysql_fetch_array($mesin)) {

            echo("<OPTION VALUE=$dbq[idMajor] selected>$dbq[idMajor]</option>\n");

            $front=substr($dbq[idMajor],0);





     }

?>

          </select>

      </td>

    </tr>

    <tr>
      <td valign="top" height="27"><b>ID

        Minor:</b></td>

      <td valign="top">
        <select name="noTag2" size="1">

          <option value="0">Choose Minor ID</option>



<?php

   //----------------------------------------------------

   //         Access Data From MySQL

   //         TABLE : tPartMinor

   //----------------------------------------------------



    $query2 = "Select * from ".$DBprefix." tPartMinor where substring(idMinor,0)='$front'";

    $mesin = mysql_query($query2);

            while ($dbq = mysql_fetch_array($mesin)) {

            echo("<OPTION VALUE=$dbq[idMinor] selected>$dbq[idMinor]</option>\n");

     }

?>



        </select>

pls help me.........

richard.york · January 7th, 2004, 02:25 AM

Well there's just one problem, PHP cannot dynamically change <select> fields on the client-side, that is when the page has loaded into the browser. PHP is a server-side language, it executes on the server long before (in internet time.. only really a few seconds.. but might as well be a thousand years for all you're conserned!) the user gets a hold of it. After it has been processed on the server and output a page to the user's browser PHP no longer has any control of what's going on in the page. For this you'll need a client-side language, like JavaScript.

Example of what I think you're trying to do:

Code:

<?php

    echo "<html>
            <head>
                <title>
                    dynamic select boxes
                </title>
                <script language='javascript' type='text/javascript'>

                    function change_other_box()
                    {
                        var second_box_elements = new Array();";

                    // Notice how I'm using PHP to create Javascript, its a language inside a language
                    // You can use PHP to output Javascript, but it cannot interact with it... at least not at PHP runtime on the server!

                    // I'm not sure about your use of substring here.. is this a valid mysql function??

                    $mesin = mysql_query("Select * from ".$DBprefix." tPartMinor where substring(idMinor,0)='$front'");

                        for ($i = 0; $dbq = mysql_fetch_array($mesin); $i++)
                        {
                            //Javascript requires strings be quoted! ~oops!
                            echo "                            second_box_elements[$i] = '{$dbq["idMinor"]}'\n;";
                        }

            echo "
                        var first_select_element = myform.the_first_box;
                        var second_select_element = myform.the_second_box;
                        var i;
                        var n;
                        var new_option;
                        var selected_default_value = 'make me the default seleted value!";

                        for (i = 0; first_select_element.length; i++)
                        {
                            if (first_select_element.options[i].selected == true)
                            {
                                // make your options based on which is selected here
                                // I'm just going to use the array I created above here and add those as options to the second menu

                                for (n = 0; second_box_elements.length; n++)
                                {
                                    new_option       = createElement('option');
                                    new_option.value = second_box_elements[n];
                                    new_option.text  = second_box_elements[n];

                                    if (second_box_elements[n] == selected_default_value)
                                    {
                                        new_option.selected = true;
                                    }

                                    second_select_element.options.add(new_option);
                                }

                                // We've found the selected value, added the options, so kill iteration

                                break;

                            }

                        }

                    }

                </script>
            </head>
            <body>
                <form method='post' name='myform' action='javascript:void(0);'>



                    <select name='the_first_box' onchange='change_other_box()'>
                        <option value='0'>Choose Major ID</option>";

                    // Where are you getting ".$DBprefix."tPartMajor"?

                $mesin = mysql_query("SELECT * FROM ".$DBprefix."tPartMajor");

                // empty - check for false, null, zero, empty string, string zero values
                // http://www.php.net/empty

                if (empty($mesin))
                {
                    // Output your errors when developing, devise a way to hide your errors in live enviornment!

                    echo mysql_error()."<br />\n";
                    echo mysql_errno()."<br />\n";

                    // MySQL error reporting is very helpful!
                    // http://www.php.net/mysql_error
                    // http://www.php.net/mysql_errno
                }

                //Don't put me *in* the loop!
                $default_value = 'Hi I\'m selected by default!';

                while ($dbq = mysql_fetch_array($mesin))
                {
                    // Is every option selected by default??
                    // echo("<OPTION VALUE=$dbq[idMajor] selected>$dbq[idMajor]</option>\n");
                    // Parenthesis are optional with echo.. I'd leave them off.. 
                    // This is because echo is a language construct, not a real function
                    // http://www.php.net/echo
                    // Also perhaps better to minimize comments inside of a loop.. 

                    $option = "<option value='{$dbq["idMajor"]}'";

                    if ($default_value == $dbq["idMajor"])
                    {
                        // Concactenating the option value with '.=' (concactenation and assignment operators) which means literally
                        // Take the existing value of this variable and add the following to it.

                        $option .= " selected='selected'";

                    }

                    // {$dbq["idMajor"]} is called curly brace syntax, 
                    // it allows PHP to drop out of normal string parsing mode and react to the contents 
                    // between the curly braces as variables.. which is handy for nested arrays
                    // http://www.php.net/manual/en/language.types.string.php

                    $option .= ">{$dbq["idMajor"]}</option>";

                    echo $option;        

                }

                echo "</select>
                    <select name='the_second_box'>

                    </select>
                </form>
            </body>
        </html>";

?>

I have not tested this for parse errors, logic errors or javascript errors, but this should give you an idea of how to do what you'd like! I also didn't look into whether your second MySQL query was valid SQL. Please let us know if you have any questions.

Best of luck!
: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::

richard.york · January 7th, 2004, 03:00 AM

Also..
To truncate the second select box with JavaScript to add a completely new set of options dynamically, you'd use the remove() method, rather than the add.

// Truncate the second selection box
// Add this before the second/nested for loop
// This method is IE specific

for (n = 0; second_box_elements.length; n++)
{
    // IE Specific Method
    if (navigator.appName == 'Microsoft Internet Explorer')
        second_select_element.options.remove(n);

    // Netscape Specific Method
    else if (navigator.appName == 'Netscape')
        second_select_element.options[n] = null;
}

I should also mention that the method I used to add option elements was also IE-only... but I'm not sure off the top of my head how to do that with Netscape.

: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::

richard.york · January 7th, 2004, 03:38 AM

I was having another look at my code and have found my logic seriously flawed.. so here's take two on the whole thing.

Code:

<?php

    echo "<html>
            <head>
                <title>
                    dynamic select boxes
                </title>
                <script language='javascript' type='text/javascript'>

                    function change_other_box()
                    {
                        var second_box_elements = new Array();";

                    // Notice how I'm using PHP to create Javascript, its a language inside a language
                    // You can use PHP to output Javascript, but it cannot interact with it... at least not at PHP runtime on the server!

                    // I'm not sure about your use of substring here.. is this a valid mysql function??
                    // Output every possible selection set of the second select box in JavaScript variables 

                    $mesin = mysql_query("Select * from ".$DBprefix." tPartMinor where substring(idMinor,0)='$front'");

                        for ($i = 0; $dbq = mysql_fetch_array($mesin); $i++)
                        {
                            echo "                            second_box_elements[$i] = '{$dbq["idMinor"]}'\n;";
                        }

            echo "
                        var first_select_element = myform.the_first_box;
                        var second_select_element = myform.the_second_box;
                        var i;
                        var selected_default_value = 'make me the default seleted value!";

                        for (i = 0; first_select_element.length; i++)
                        {
                            // Add a condition based on the value of this particular element

                            if (first_select_element.options[i].selected == true)
                            {
                                switch (first_select_element.options[i].value)
                                {
                                     case 'A value of the first select box':

                                        // Use functions so that you will be able to reuse code

                                            truncate_select(the_select_element);
                                            populate_select(second_select_element, second_box_elements, selected_default_value);

                                            break;

                                    // Repeat the switch statement for every value of the first select box
                                    // create new arrays for each value
                                }


                                // We've found the selected value, added the options, so kill iteration

                                break;

                            }

                        }

                    }

                    function populate_select(the_select_element, the_options, default_value)
                    {
                        var new_option;
                        var n;

                        for (n = 0; the_options.length; n++)
                        {
                            new_option       = createElement('option');
                            new_option.value = the_options[n];
                            new_option.text  = the_options[n];

                            if (the_options[n] == default_value)
                            {
                                new_option.selected = true;
                            }

                            the_select_element.options.add(new_option);
                        }

                        return true;
                    }

                    function truncate_select(the_select_element)
                    {
                        var n;

                        for (n = 0; the_select_element.length; n++)
                        {
                            // IE Specific Method
                            if (navigator.appName == 'Microsoft Internet Explorer')
                                the_select_element.options.remove(n);

                            // Netscape Specific Method
                            else if (navigator.appName == 'Netscape')
                                the_select_element.options[n] = null;
                        }

                        return true;
                    }

                </script>
            </head>
            <body>
                <form method='post' name='myform' action='javascript:void(0);'>



                    <select name='the_first_box' onchange='change_other_box()'>
                        <option value='0'>Choose Major ID</option>";

                    // Where are you getting ".$DBprefix."tPartMajor"?

                $mesin = mysql_query("SELECT * FROM ".$DBprefix."tPartMajor");

                // empty - check for false, null, zero, empty string, string zero values
                // http://www.php.net/empty

                if (empty($mesin))
                {
                    // Output your errors when developing, devise a way to hide your errors in live enviornment!

                    echo mysql_error()."<br />\n";
                    echo mysql_errno()."<br />\n";

                    // MySQL error reporting is very helpful!
                    // http://www.php.net/mysql_error
                    // http://www.php.net/mysql_errno
                }

                $default_value = 'Hi I\'m selected by default!';

                while ($dbq = mysql_fetch_array($mesin))
                {        
                    // Is every option selected by default??
                    // echo("<OPTION VALUE=$dbq[idMajor] selected>$dbq[idMajor]</option>\n");
                    // Parenthesis are optional with echo.. I'd leave them off.. 
                    // This is because echo is a language construct, not a real function
                    // http://www.php.net/echo
                    // Also perhaps better to minimize comments inside of a loop.. 

                    $option = "<option value='{$dbq["idMajor"]}'";

                    if ($default_value == $dbq["idMajor"])
                    {
                        // Concactenating the option value with '.=' (concactenation and assignment operators) which means literally
                        // Take the existing value of this variable and add the following to it.

                        $option .= " selected='selected'";

                    }

                    // {$dbq["idMajor"]} is called curly brace syntax, 
                    // it allows PHP to drop out of normal string parsing mode and react to the contents 
                    // between the curly braces as variables.. which is handy for nested arrays
                    // http://www.php.net/manual/en/language.types.string.php

                    $option .= ">{$dbq["idMajor"]}</option>";

                    echo $option;        

                }

                echo "</select>
                    <select name='the_second_box'>

                    </select>
                </form>
            </body>
        </html>";

?>

: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::

nikolai · January 7th, 2004, 02:49 PM

I just have something simple to add. If all you need is a single character from a string, it's a little more efficient to access that character using it's numeric index in the string. That is to say, you can treat a string as a numerically-indexed array of characters.

For example, the first character of the string, $my_string, is $my_string[0].

Also, in addition to Rich's javascript code, you should read a very similar thread that deals with different methods of having a <select> dropdown field automatically change the value of another <select> dropdown.
http://p2p.wrox.com/topic.asp?TOPIC_ID=8040

Take care,

Nik
http://www.bigaction.org/

apek · January 11th, 2004, 04:44 AM

ah..i'm so dizzy..
is there any way simpler than this??

richard.york · January 11th, 2004, 01:10 PM

Well this way is pretty simple! If you take a moment to read and understand the code. Your English is fine, by the way.

If you want to fetch only the records starting with 'A' do something like this, which will be faster than using a regular expression in the query:

"SELECT idMajor FROM ".$DBprefix." tPartMinor WHERE idmajor LIKE 'A%' GROUP BY idmajor ORDER BY idmajor";

The percentage sign is a wild-card character, consider the following;
LIKE '%A%' Begins with any character(s), contains an A, ends with any character(s)
LIKE 'A%' Begins with A, ends with any character(s).
LIKE '%A' Begins with any character(s), ends with 'A'

So you don't really need the substring.

It helps to keep your SQL syntax, namely keywords in uppercase, so that they are easy to distinguish from other parts of the query.

What are you having trouble understanding?

Quote:

quote:
$query2 = "Select LEFT('idMinor',0) from ".$DBprefix." tPartMinor where LEFT('idMinor',0)='$minor'";
   $result2 = mysql_query($query2);
         while ($dbq2 = mysql_fetch_array($result2)) {
         echo("<OPTION VALUE=$dbq2[idMinor] selected>$dbq2[idMinor]</option>\n");
      }

This isn't a good way to build a <select> element for reasons already mentioned. You don't want to select every element by default and you should delimit the value with quotations. A proper use of this is already demonstrated in above code.

Quote:

quote:
my problem is that the front should be not "W"...i chose B,E,R(anything except W) at the submit page but it keeps sending W.
Why?

Well this *might* have something to do with your making every element selected by default. Let's work through the solution already provided step by step starting with whatever you find most confusing.

: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::

apek · January 11th, 2004, 11:37 PM

Quote:

quote:Well this *might* have something to do with your making every element selected by default

how to fix that???

richard.york · January 12th, 2004, 12:25 AM

Quote:

quote:
how to fix that???

Use this snippet of code.

Code:

//...
                $mesin = mysql_query("SELECT * FROM ".$DBprefix."tPartMajor");

                if (empty($mesin))
                {                
                    echo mysql_error().": ";
                    echo mysql_errno()."<br />\n";
                }

                $default_value = "Hi I\'m selected by default!";

                while ($dbq = mysql_fetch_array($mesin))
                {                    
                    $option = "<option value='{$dbq["idMajor"]}'";

                    if ($default_value == $dbq["idMajor"])
                    {                    
                        $option .= " selected='selected'";
                    }

                    $option .= ">{$dbq["idMajor"]}</option>";

                    echo $option;        

                }
//...

The $default_value variable is what you need to set as the default value, then as your option list is built every possible option will be compared to that value, if a match is made then the current value of $dbq["idMajor"] will be made the default selected value.

If you want no options selected, then remove the 'selected' attribute.

So to demonstrate, this code will produce html output like this:

<select name='the_first_box' onchange='change_other_box()'>
       <option value='0'>Choose Major ID</option>
       <option value='result1'>result1</option>
       <option value='result2'>result2</option>
</select>

And so on..

If you make the value of the $default_value variable this:
$default_value = "result1";

Then this will be the output:
<select name='the_first_box' onchange='change_other_box()'>
       <option value='0'>Choose Major ID</option>
       <option value='result1' selected='selected'>result1</option>
       <option value='result2'>result2</option>
</select>

result1 will be highlighted in the select box.

If you want 'Choose Major ID' to be the selected value, then remove the portion that says 'selected' like so (no need for the extra code in the wihle loop):

while ($dbq = mysql_fetch_array($mesin))
{
    echo "<option value='{$dbq["idMajor"]}'>{$dbq["idMajor"]}</option>\n";
}

Make sense?
: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::

apek · January 12th, 2004, 06:40 AM

i change my code according to your opinion like this:

Code:

          <select name="noTag" size="1" >
            <?php

   //----------------------------------------------------
   //          Access Data From MySQL
   //          TABLE : tPartMajor
   //----------------------------------------------------
   $mesin = mysql_query("SELECT * FROM ".$DBprefix."tPartMinor group by idMajor order by idMajor");

                if (empty($mesin))
                {                
                    echo mysql_error().": ";
                    echo mysql_errno()."<br />\n";
                }

                $default_value = "Hi I\'m selected by default!";

                while ($dbq = mysql_fetch_array($mesin))
                {                    
                    $option = "<option value='{$dbq["idMajor"]}'";

                    if ($default_value == $dbq["idMajor"])
                    {                    
                        $option .= " selected='selected'";
                    }

                    $option .= ">{$dbq["idMajor"]}</option>";

                    echo $option;        




            $ayam=$dbq[idMajor];
            $depan=substr($ayam,0,1);
                                    }
?>
          </select>

but u know what...it still pass only "W" to the next page...even i chose B,E or R,it still pass only "W" to the next page..it seems like the W is the default and static value...

i post my prob here in other php forum on net,and some people reply to me and said

Quote:

quote:I've now detected the problem.

Quote:

The thing is you are always updating the value of $depan inside the cycle so the value that will show in $_POST['front'] will always be the result of the last iteration.

You must review what you want from front and then rearrange the code.

what does he mean??and how to fix the updating the value of $depan inside the cycle he not reply until now...