How to assign specific dbs value to multiple variables without using multiple query statements?

Question

I'm currently creating an Admin Panel and I'm coding it to be 100% fool proof. You'll be able to edit every piece of information through editing phrases from the database.

The main way I know is to use this:

$c = $conn->query('SELECT phrase FROM phrases WHERE phrase_name = "corp_name"') or trigger_error($conn->error);

$d = $c->fetch_array();

$phrase_corp_name = $d['phrase']; // "Corporation"

This is not very ideal when I'm going to have dozens of phrases.

The issue is, when I try to select a phrase like so, it does nothing:

 $phrase_corp_name = mysqli_query($conn, "SELECT phrase FROM phrases WHERE phrase_name = 'corp_name'");

This should ideally output, "Corporation," which is the value of "phrase" in this instance. I'm assuming the code is wrong, but I can't wrap my head around it.

The error I get here is:

Recoverable fatal error: Object of class mysqli_result could not be converted to string in ... on line 94

You always have to use `fetch` to retrieve the values that were selected. You can't just print the result of `$conn->query()`, it's an intermediate object. — Barmar, Oct 31 '19 at 23:49
What does that have to do with your question about fetching dozens of phrases? — Barmar, Oct 31 '19 at 23:52
@Barmar Isn't mysqli_query separate from conn-query()? If not, how would I utilize $d to select single values and not just a column? — Bellator, Oct 31 '19 at 23:52
No, `mysqli_query` and `$conn->query` are just two different ways of writing the same thing. mysqli allows you to use both procedural and OO syntax, but they're completely interchangeable. — Barmar, Oct 31 '19 at 23:52
Ok, before the comment section becomes a train wreck, could you please explain what is in `phrases`, how do you want to use the values from there, what is the current behaviour of your script and why is it not what you want. — Dharman, Oct 31 '19 at 23:53
If you want to look up multiple phrases, you can use `WHERE phrase_name IN ('phrase1', 'phrase2', 'phrase3')`. Then you can fetch the results in a loop to get all the results. — Barmar, Oct 31 '19 at 23:54
You have to write `$row = mysqli_fetch_assoc($phrase_corp_name); echo $row['phrase'];` — Barmar, Oct 31 '19 at 23:55
@Dharman The phrases.php file will simply contain a list of variables with the assignment of their database value counterpart. I will then use this file as an include on most pages and reference them via their variable name. I want to do this using the least code possible. — Bellator, Oct 31 '19 at 23:59
@Barmar How would that code look if you were assigning it to a variable, without echo. You'll use the variable's assignment to echo it out. — Bellator, Nov 01 '19 at 00:02

score 2 · Accepted Answer · answered Nov 01 '19 at 00:05

A single variable for a single row in the database is completely unnecessary. You can use associative arrays for this purpose; they are perfect for such task.

Get all the results from your phrases table into an array and then access the phrases using keys.

$c = $conn->query('SELECT phrase_name, phrase FROM phrases') or trigger_error($conn->error);
$phrases = [];
foreach ($c as $row) {
    $phrases[$row['phrase_name']] = $row['phrase'];
}

and then access the values like this:

echo $phrases['corp_name'];

As a side note, you would be better off enabling mysqli error reporting, rather than doing it manually yourself. Please read How to get the error message in MySQLi?

How to assign specific dbs value to multiple variables without using multiple query statements?

1 Answers1