PHP to SQL request

Question

So I am new to this and I wanted to use the ID of my li elements to make a request to SQL but realized too late that was not possible. Now I am stuck and I don't know how to proceed other than by doing this the long way and coding each li individually.

Here is my code:

         <li style="float: right;">Themes
            <ul>

             <?php
                $id_count = 0;
                $db=mysql_connect('localhost','root','');
                if(!$db) {
                    die('Could not connect: '.mysql_error());
                }
                $connection_string=mysql_select_db('Mydb',$db);
                $selectSQL='SELECT * FROM color_patterns';       
                $queryset=mysql_query($selectSQL);
                $num=mysql_num_rows($queryset);
                if(0==$num) {
                    echo "No record";
                    exit;
                } else {
                    while($row=mysql_fetch_assoc($queryset)) {?>
                    <li id="<?php echo($id_count) ?>" onclick="
                              $sql='SELECT * FROM color_elements WHERE pat_ID = "the index of the li"';       
                              $query=mysql_query($sql);
                              $hex1 = "hex code in row 1"
                              $hex2 = "hex code in row 2"
                              $hex3 = "hex code in row 3"">
                        <?php echo($row['name']); 
                        $id_count++;
                        ?></li><?php
                    }

                }
            ?>


            </ul>
        </li>

The themes list element has a dropdown list which is populated by the while loop (it creates a new li for each row in the table color_patterns and puts the name of the row as the title of the li, this part works perfectly). Now when the user clicks on one of the li in the dropdown I wanted to send a SQL request to the color_elements table where the id = "the clicked li index" and it will retrieve a table with 3 rows and and I would assign the value in the "hex_code" column from each row to one of 3 php variables. I can't think of anyway to do this (mainly send the clicked li index to SQL) is it possible or will I have to do it another way (I know another way but it's ugly and long).

For example: My color_patterns table has 2 rows with names A and B,

the while loop creates 2 li labeled one labeled A and the other B, When the user clicks A a sql command is run:

   $sql='SELECT * FROM color_elements WHERE pat_ID = 1'; //here 1 is the index of A

this returns a table with 3 rows and I use the "hex_code" column to assign $hex1,$hex2,$hex3 values based on the 1st, 2nd and 3rd row.

Any help at all is appreciated. Thank you.

Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jun 15 '16 at 19:07
JavaScript is client side and PHP is server side. You cannot perform a query directly in JavaScript, instead you would use AJAX to capture the necessary data (as I showed you previously) and then write an AJAX request to a PHP script which will query the database. — Jay Blanchard, Jun 15 '16 at 19:10
I wasn't aware of PDO I'll read up on it but does it solve the issue of the li index? that's the biggest problem I have is I don't know how to send the index of li to SQL when the user clicks on the li — H.H., Jun 15 '16 at 19:12
No - it doesn't solve that problem. If you want to send the index to SQL you need to [learn AJAX](http://jayblanchard.net/basics_of_jquery_ajax.html) — Jay Blanchard, Jun 15 '16 at 19:12
I realized I couldn't use JavaScript and am trying to code it purely in php as and I've never used AJAX before. I'll try to go back to the JS method and try to do it using AJAX — H.H., Jun 15 '16 at 19:14
You don't have to use AJAX, you can do it with a form and a page reload, which is standard for PHP. — Jay Blanchard, Jun 15 '16 at 19:16
In your code above 'pat_ID' has nothing to do with each li's id.. You loop around and insert the counter in each li. You should use the 'pat_ID' and send from the client over to the server to achieve what you want — llouk, Jun 15 '16 at 19:22

score 0 · Answer 1 · answered Jun 15 '16 at 22:49

while($row=mysql_fetch_assoc($queryset)) {?>
                    <li id="<?php echo($id_count) ?>" onclick='
                              $sql="SELECT * FROM color_elements WHERE pat_ID = $id_count"';       
                              $query=mysql_query($sql);
                              $hex1 = "hex code in row 1"
                              $hex2 = "hex code in row 2"
                              $hex3 = "hex code in row 3"">
                        <?php echo($row['name']); 
                        $id_count++;
                        ?></li><?php
                    }

Why do you want to get the value from MySQl if it's available in PHP?

PHP to SQL request

1 Answers1