CODEWITHSUNDEEP
phpmysqlhtmlsql
GK1667
GK1667

Reputation: 1362

Insert dynamically MySQL-PHP combination

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.

Upvotes: 0

Views: 163

Answers (5)

flec
flec

Reputation: 3019

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

Upvotes: 1

Falci
Falci

Reputation: 1873

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

Upvotes: 1

Igor Parra
Igor Parra

Reputation: 10348

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

Upvotes: 2

fehrlich
fehrlich

Reputation: 2525

Or you can use

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

Upvotes: 2

Paul Dessert
Paul Dessert

Reputation: 6389

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

Upvotes: 0

Related Questions