Tag: select
Nabin Nepal (Starx) mysql php query select
May 27, 2013

How to provide table name to select from a search text box like: SELECT * FROM $query

User2425381’s Question:

user2425381user2425381

I cant find the answer anywhere, maybe someone could help me. I need to display my MySQL table, but I need to give the name of the table through search box.
Something like this:

$raw_results = mysql_query("SELECT * FROM $query") or die(mysql_error());
StarxStarx

Your code already does that, only thing remaining is to pass the value to the variable $query

$query = "your search query";

$raw_results = mysql_query("SELECT * FROM $query") or die(mysql_error());

However, what you are doing is very vulnerable, you should not pass the table name from the text box to the code directly.

$query = $_POST['searchbox'];

Doing such will leave your code very vulnerable as I could type in users and get all the details of the user.

Change your programming, whatever you are trying to do is wrong.

Nabin Nepal (Starx) encode json pdo php select
February 25, 2013

PHP PDO data into Json_Encode + but only first line appears

Question by Adam

AdamAdam
                       $sql='SELECT word,language,acceptable FROM profanity;';
                       $pds=$database_miscellaneous->pdo->prepare($sql); 
                       $pds->execute(); 
                       //$row=$pds->fetch();
        foreach($pds as $row) {
            $profanityText = json_encode(array('word' => $row['word'],
                                       'language' => $row['language'],
                                       'acceptable' => $row['acceptable']));
        }

I have the above code. The catch is it works but only the first line from the database goes into the json array. I’ve run the query directly against the DB and it pulls all the data.

I’m guessing my foreach loop has an issue or many the way I’m doing the PDO call.

any ideas?

Answer by Starx

StarxStarx

The problem is that the variable holding the json encoded values $profanityText gets overidden every time in the loop.

Use this

    $rows = array()
    foreach($pds as $row) {

        $rows[] = array('word' => $row['word'],
            'language' => $row['language'],
            'acceptable' => $row['acceptable']);
    }
    $profanityText = json_encode($rows);

Or, if you are not manipulating your data in anyway, you can just directly call (as mentioned by deceze)

$profanityTest = json_encode($pds->fetchAll(PDO::FETCH_ASSOC));
Nabin Nepal (Starx) loops option php select
June 17, 2012

PHP loop within a loop, option selected?

Question by David

DavidDavid

Using PHP I echo out table rows in a loop like this:

<?php
/* SQL STUFF */

  while ($row = mysql_fetch_array($select_courseelements)) {
   echo "<tr>n";
    echo "<td>".$row['scpe_name']."</td>n";
    echo "<td>".$row['scpe_days']."</td>n";
   echo "</tr>n";
  }

Now I would like to include a <select> element with 5 predefined <option> values inside a <td> running with the loop. The option values will be 1 to 5.

There is also a column inside the $row loop that holds a value of 1 to 5 ($row['scpe_grades_status']).

Each time this value is equal to the one in the <select> I want it to change it to selected='selected'.

Would this be possible?

My <select> will look something like this when it’s beeing run in the loop:

echo "<td>n";
echo "<select id='elements_grade'>n";
        echo "<option value='1'>Registrerad</option>n";
        echo "<option value='2'>Ej påbörjad</option>n";
        echo "<option value='3'>Pågående</option>n";
        echo "<option value='4'>Godkänd</option>n";
        echo "<option value='5'>Deltagit</option>n";
        echo "<option value='6'>Ej deltagit</option>n";
echo "</select>n";
echo "</td>n";

Answer by piers

pierspiers
$array = array('Registrerad' => 1, 'Ej påbörjad' => 2, 'Pågående' => 3, 'Godkänd' => 4, 'Deltagit' => 5, 'Ej deltagit' => 6);

foreach ($array as $key=>$value) {
    if ($value == $row['scpe_grades_status'])
        echo '<option value="'.$value.'" selected>'.$key.'</option>';
    else
        echo '<option value="'.$value.'">'.$key.'</option>';
}

Something like that?

Answer by Starx

StarxStarx

Sure, build the values from a loop. and you can compare the values from that part.

for($i = 1; $i<=5; $i++) {
   echo "<option value='$i'";
   echo ($row['scpe_grades_status'] == $i) ? " selected='selected'": "";
   echo ">...."</option>"
}
Nabin Nepal (Starx) mysql row-number select
May 4, 2012

Select specific row from mysql table

Question by Marlon Brando

Marlon BrandoMarlon Brando

Ideally I need a query that is equal to 

select * from customer where row_number() = 3

but thats illegal.

I cant use an auto incremented field.

row_number() is the row that needs to be selected.

How do I go about this?

EDIT: Well, I use iSql*plus to practice, and using limit and auto_increment is illegal for some reason. I ended up creating a sequence and a trigger and just uped the id by 1 everytime there was an entry.

Answer by Starx

StarxStarx

You cannot select a row like that. You have to specify a field whose values will be 3

Here is a query that will work, if the field you are comparing against is id

select * from customer where `id` = 3
Nabin Nepal (Starx) dynamic list option php select
April 20, 2012

Dynamic Select list using sql data

Question by Baruch

BaruchBaruch

I am trying to create a select drop-down list using php. Every time i try, i get an error.
Here is my code:

The function:

function dropDown(){


$options="<select>"; 
$connect = mysql_connect('localhost','id','pass') or die ("couldn't   connect!").mysql_error; 

mysql_select_db('db') or die('could not connect to database!');


$sql="SELECT * FROM DESC"; 
$result=mysql_query($sql); 


while ($row=mysql_fetch_array($result)) {  ****this is line 60

$name=$row["name"]; 

$options.="<option value="$name">".$name."</option>"; 
} 

$options.= "</SELECT>";
return "$options";
}

and then i just call it in my code

<?php  
 include ('includes/functions.php');
....



$list = dropDown();
echo "$list";


....
>

the error i get is:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/garagenj/public_html/dispatch/includes/functions.php on line 60

Answer by JT Smith

JT SmithJT Smith

If your table name is DESC then that’s your problem. It’s all about namespacing. You can’t even have a field named desc, I’ve tried. It errors everytime

Answer by Starx

StarxStarx

SELECT * FROM DESC ??????

  • From what table
  • Or, if the table name is DESC. escape them with ticks

And You dont have start a new connection, every time you call the function. Put your connection part somewhere else

$connect = mysql_connect('localhost','id','pass') or die ("couldn't   connect!").mysql_error; 
mysql_select_db('db') or die('could not connect to database!');

function dropDown(){
    $options="<select>"; 
    $sql="SELECT * FROM `DESC`"; 
    $result=mysql_query($sql); 

    while ($row=mysql_fetch_array($result)) { 

        $name=$row["name"]; 

        $options.="<option value="$name">".$name."</option>"; 
    } 

    $options.= "</SELECT>";
    return $options;

}
Nabin Nepal (Starx) mysql optimization select
April 8, 2012

Most efficient way to LIMIT results in a JOIN?

Question by johnnietheblack

johnnietheblackjohnnietheblack

I have a fairly simple one-to-many type join in a MySQL query. In this case, I’d like to LIMIT my results by the left table.

For example, let’s say I have an accounts table and a comments table, and I’d like to pull 100 rows from accounts and all the associated comments rows for each.

Thy only way I can think to do this is with a sub-select in in the FROM clause instead of simply selecting FROM accounts. Here is my current idea:

SELECT a.*, c.* FROM 
    (SELECT * FROM accounts LIMIT 100) a
    LEFT JOIN `comments` c on c.account_id = a.id
    ORDER BY a.id

However, whenever I need to do a sub-select of some sort, my intermediate level SQL knowledge feels like it’s doing something wrong.

Is there a more efficient, or faster, way to do this, or is this pretty good?

By the way…

This might be the absolute simplest way to do this, which I’m okay with as an answer. I’m simply trying to figure out if there IS another way to do this that could potentially compete with the above statement in terms of speed.

Answer by Priyank

PriyankPriyank

Looks perfect to me.

Just wondering if it is ok with you to receive less or more than 100 rows from the above query.
Because if for a particular row of accounts table there are no rows in comments table then you would get less than 100 rows. Similarly if there are multiple matching rows in comments table for a row in accounts table then you may get more than 100 rows.

See: How can I optimize this query, takes more than a min to execute

Answer by Starx

StarxStarx

No, The query is fine, just the way it is. You might want to filter the fields instead of a.* and c.* though.

Nabin Nepal (Starx) forms jquery onchange option select
March 28, 2012

Jquery form onchange select values changing

Question by Malyo

MalyoMalyo

I’m looking for best solution to this problem. I have a simple shop logical problem. There are 2 select elements, size and color. I want to make them dependent, on data (now it’s example data, but later it’s gonna be from database) – size will decide which color options will be visible for customer (hiding not necessary ones).

First problem is that when i make change event, and i wanna hide the default shown element on document ready, it’s still visible (i’d have to change color to different than open dropdown again and it won’t be visible then).

Second is that i’m looking for most flexible solution, since i have doubts about mine. Here’s the code:

       var rozmiar = new Array("S", "M", "L", "XL", "XXL");
   var kolor = new Array("Czerwony", "Niebieski", "Zielony", "Biały", "Czarny");
   var opcje = new Array( rozmiar, kolor);

        $(document).ready(function(){
        $('.form1').change(function(){
                $('.form2 option').show();

                var selectSelector = function(z){
                    selectSelector = $('select.form2 option[value='+kolor[z]+']').hide();
                };

                wybranyRozmiar = $(this).val();
                    if(wybranyRozmiar == rozmiar[0]){
                        selectSelector(0);
                    }
                    if(wybranyRozmiar == rozmiar[1]){
                        selectSelector(1);
                    }
                    if(wybranyRozmiar == rozmiar[2]){
                        selectSelector(2);
                    }
                    if(wybranyRozmiar == rozmiar[3]){
                        selectSelector(3);
                    }
                    if(wybranyRozmiar == rozmiar[4]){
                        selectSelector(4);
                    }
            });
        });

Answer by Starx

StarxStarx

I am answering the only part I understand.

Instead of using multiple if statements you can use switch

switch(selectsize) {
   case rozmiar[1]:
       $('select.form2 option[value='+color[2]+']').hide();
       break;
   //case <another>"
       //break;
}
Nabin Nepal (Starx) arrays mysql php select
March 24, 2012

Select where in array

Question by Jordy

JordyJordy

How can I select all records where the id is in the array?

So if I have an array with the values 1,25,2,12,1859 and 192: all the records with the id’s should be returned.

Now I have this: 

$query="SELECT * FROM table WHERE Id IN(".implode(",",$my_array).")";

But that doesn’t work. It only returns one value.

Answer by Starx

StarxStarx

Add the quotes in the query

$query="SELECT * FROM table WHERE Id IN('".implode("','",$my_array)."')";
Nabin Nepal (Starx) mysql select
March 12, 2012

SELECT command in mysql

Question by Jordashiro

JordashiroJordashiro

I was wondering if there is a way to do something like selecting all without … some columns here

something like SELECT */column1,column2 , is there a way to do this ?
I just need to output something like

column1 , column2 ( from another table ) , here all other columns without column1 ( or something to make the select skip the first few columns)

EDIT:
The thing is that i need this to be dynamic , so i cant just select what i don’t know. I never know how many columns there will be , i just know the 1st and the 2nd column

EDIT: here is a picture http://oi44.tinypic.com/xgdyiq.jpg
I don’t need the second id column , just the last column like i have pointed.

Answer by DanRedux

DanReduxDanRedux

Oh, so select all but certain fields. You have two options.

One is a little slow.. Copy the table, drop the fields you don’t want, then SELECT *
The other is to build the field list from a subquery to information_schema or something, then remove occurrences of ‘field_i_dont_want’ in that list.

SELECT ( SELECT THE TABLES YOU WANT AND CONCAT INTO ONE STRING ) FROM TABLE

Answer by Starx

StarxStarx

If you need to combine records from multiple tables, you need to find a way to relate them together. Primary Keys, Foreign Keys, or anything common among this.

I will try to explain this with a sql similar to your problem.

SELECT table1.id, table2.name, table1.column3, table1.column4 
FROM table1
INNER JOIN table2 On table2.commmonfield = table1.commonfield
Nabin Nepal (Starx) arrays element php select
June 22, 2011

Remembering options in a select box array after submitting through php

Question by Marcus Edensky

Marcus EdenskyMarcus Edensky
<form method="post">
    <select name="box[]">
        <option value="1" <?php if ($_POST['box[0]'] == "1") echo "selected="selected";"?>>1</option>
        <option value="2" <?php if ($_POST['box[0]'] == "2") echo "selected="selected";"?>>2</option>
        <option value="3" <?php if ($_POST['box[0]'] == "3") echo "selected="selected";"?>>3</option>
    </select>
    <p>
    <select name="box[]">
        <option value="1" <?php if ($_POST['box[1]'] == "1") echo "selected="selected";"?>>1</option>
        <option value="2" <?php if ($_POST['box[1]'] == "2") echo "selected="selected";"?>>2</option>
        <option value="3" <?php if ($_POST['box[1]'] == "3") echo "selected="selected";"?>>3</option>
    </select>
    <p>
    <input type="submit" value="Submit">
</form>

When I use box names “box1” and “box2”, it works without a problem. What am I doing wrong?

****** EDIT ********

Thanks a lot for your comments, but I actually found the solution myself, even if it doesn’t make much sense. Instead of using $_POST[‘box’][0] and [1] at the if statement, I simply used $box[0] and [1]. Even though it’s posted, apparently php sees it as a normal array, and not as some kind of $_POST-array! Working code:

<form method="post">
    <select name="box[]">
        <option value="1" <?php if ($box[0] == "1") echo "selected='selected'";?>>1</option>
        <option value="2" <?php if ($box[0] == "2") echo "selected='selected'";?>>2</option>
        <option value="3" <?php if ($box[0] == "3") echo "selected='selected'";?>>3</option>
    </select>
    <p>
    <select name="box[]">
        <option value="1" <?php if ($box[1] == "1") echo "selected='selected'";?>>1</option>
        <option value="2" <?php if ($box[1] == "2") echo "selected='selected'";?>>2</option>
        <option value="3" <?php if ($box[1] == "3") echo "selected='selected'";?>>3</option>
    </select>
    <p>
    <input type="submit" value="Submit">
</form>

Answer by Marcus Edensky

Marcus EdenskyMarcus Edensky

Thanks a lot for your comments, but I actually found the solution myself, even if it doesn’t make much sense. Instead of using $_POST[‘box’][0] and [1] at the if statement, I simply used $box[0] and [1]. Even though it’s posted, apparently php sees it as a normal array, and not as some kind of $_POST-array! Working code:

<form method="post">
    <select name="box[]">
        <option value="1" <?php if ($box[0] == "1") echo "selected='selected'";?>>1</option>
        <option value="2" <?php if ($box[0] == "2") echo "selected='selected'";?>>2</option>
        <option value="3" <?php if ($box[0] == "3") echo "selected='selected'";?>>3</option>
    </select>
    <p>
    <select name="box[]">
        <option value="1" <?php if ($box[1] == "1") echo "selected='selected'";?>>1</option>
        <option value="2" <?php if ($box[1] == "2") echo "selected='selected'";?>>2</option>
        <option value="3" <?php if ($box[1] == "3") echo "selected='selected'";?>>3</option>
    </select>
    <p>
    <input type="submit" value="Submit">
</form>

Answer by Starx

StarxStarx

Both, elements have same name. Thats the problem.
$_POST['box[0]'] , $_POST['box[1]'] , contains the array of the two elements, not the value it self.

...

Please fill the form - I will response as fast as I can!