Insert dynamically MySQL-PHP combination

Question

I have a custom html page which posts to php variables which then fills out custom sql queries.

function load_table($db, $old_table, $startRow, $nameColumn, $ipColumn, $addressColumn, $cityColumn, $stateColumn, $zipColumn, $countryColumn)
{
$select = "SELECT $nameColumn, $ipColumn, $addressColumn, $cityColumn, $stateColumn, $zipColumn, $countryColumn FROM " .$old_table.";";
$q = mysqli_query($db, $select);

return $q;
}

It works perfectly when all the variables are holding a value, but I need a way to dynamically assert this query, so that if the user is missing a column, i.e zip code is not in their table, it will still run without ruining the query.

Igor Parra · Accepted Answer · 2012-07-03 18:24:59Z

2

$array = array();

if ($nameColumn)
    $array[] = $nameColumn;
if ($ipColumn)
    $array[] = $ipColumn;

// etc...

$cols = implode(',', $array);

if ($cols)
{
    $select = "SELECT $cols FROM $old_table;";
    $q = mysqli_query($db, $select);
    return $q;
}

answered Jul 3, 2012 at 18:24

Igor Parra

10.4k10 gold badges74 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mahn Over a year ago

This is the proper way of doing it.

GK1667 Over a year ago

I have yet to try it but this makes the most sense! Thanks so much!

Mahn Over a year ago

Actually, this could be improved using func_get_args as pointed out by Falci.

fehrlich · Accepted Answer · 2012-07-03 18:24:19Z

2

Or you can use

$select = "SELECT ".(($nameColumn != '')?$nameColumn.",":"")." .......";

answered Jul 3, 2012 at 18:24

fehrlich

2,5352 gold badges26 silver badges45 bronze badges

Comments

Community · Accepted Answer · 2023-11-14 22:34:04Z

1

See also: https://www.php.net/manual/en/function.func-get-args.php

edited Nov 14, 2023 at 22:34

CommunityBot

11 silver badge

answered Jul 3, 2012 at 18:26

Falci

1,8734 gold badges29 silver badges56 bronze badges

Comments

flec · Accepted Answer · 2012-07-03 18:30:17Z

1

Put all your variables in an array then do the following:

function load_table($db, $old_table, $startRow, array $columns) {
    $columns = array_filter($columns, 'strlen'); // removes empty values
    $select = "SELECT " . implode(",", $columns) . " FROM " .$old_table.";";
    $q = mysqli_query($db, $select);
    return $q;
}

edited Jul 3, 2012 at 18:30

answered Jul 3, 2012 at 18:22

flec

3,0191 gold badge24 silver badges30 bronze badges

3 Comments

GK1667 Over a year ago

I am unfamiliar with the implode function. Does it simply remove variables from the array which are null?

GK1667 Over a year ago

Then how would this help with my issue? The issue is that if one of the variables does not have a value, the sql query will throw an error because the value of the php variable is null

flec Over a year ago

@user1475765 I've updated my answer that it removes empty values

Paul Dessert · Accepted Answer · 2012-07-03 18:22:24Z

0

You can give it a default:

function load_table($db, $old_table, $startRow, $nameColumn="Default name", $ipColumn, $addressColumn, $cityColumn, $stateColumn, $zipColumn, $countryColumn)

In this case $nameColumn will always have a value unless otherwise specified

answered Jul 3, 2012 at 18:22

Paul Dessert

6,3879 gold badges50 silver badges74 bronze badges

1 Comment

GK1667 Over a year ago

Thanks for your response! This sounds good, but my concern is that with a default name, and no guarentee the default name is in the table, an error will occur because it will try to look up that column name and will not find it. Unless theres a default name I can give it which will simply act as a placeholder?

Collectives™ on Stack Overflow

Insert dynamically MySQL-PHP combination

5 Answers 5

3 Comments

Comments

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related