Simple Query's

[Jamora1124]

Okay, I'm having some trouble creating a query for a page I am creating. The basics of the page is to collect information from a table I inserted into the database, and then display the information on the page. Sort of how the memberlist works.

My first question is, are we allowed to input our own tables into the database for the forum?

And my second question is, how to I query the database without an array. Let's say if I want to get one row of information? I'm very new to coding but here is my attempt at trying to query with smf's $smcFunc['db_query'] function.

Koodi [Valitse] Laajenna


$result = $smcFunc['db_query']('', 'SELECT f_name FROM {db_prefix}rpg_characters WHERE member_name = Jamal');


I've tried this though, for obvious reasons it doesn't work. I would simply like to know what is the easiest way to call information from a database?

[MrPhil]

are we allowed to input our own tables into the database for the forum?

No, the police will be around shortly to arrest you for the crime of desecrating a sacred SMF database.

Of course you can put whatever you want into an SMF database, but it's up to you to be careful to avoid damaging SMF's tables.

The "$result" you get back has to be read with certain functions to get back each row's returned by the query. Each row will be an array. In your case it will have one element of name "f_name".  That's just the way it works. Simply copy a use of database functions from SMF.

[emanuele]

And my second question is, how to I query the database without an array. Let's say if I want to get one row of information?

And the answer is...it depends.
It depends on many factors, but let's concentrate on the actual example that is the one that matters.

Koodi [Valitse] Laajenna


$result = $smcFunc['db_query']('', 'SELECT f_name FROM {db_prefix}rpg_characters WHERE member_name = Jamal');


I've tried this though, for obvious reasons it doesn't work. I would simply like to know what is the easiest way to call information from a database?

mmm..."it doesn't work" means nothing.
I'm pretty sure the code works, you probably are not getting what you expect to obtain, but that doesn't mean it doesn't work.

Since we are working with SMF let's do it "the SMF way":

Koodi [Valitse] Laajenna

$result = $smcFunc['db_query']('', '
SELECT f_name
FROM {db_prefix}rpg_characters
WHERE member_name = {string:a_name}',
array(
'a_name' => 'Jamal'
)
);
$my_result = $smcFunc['db_fetch_assoc']($request);
$smcFunc['db_free_result']($request);
print_r($my_result);

Smashing it down in pieces:

Koodi [Valitse] Laajenna

$result = $smcFunc['db_query']('', '
SELECT f_name
FROM {db_prefix}rpg_characters
WHERE member_name = {string:a_name}',
array(
'a_name' => 'Jamal'
)
);
The query. You are asking to the database to see if what you want is actually present.
SMF uses this format, where variables (i.e. the name you are looking for) are passed in a separated array, so that they can be "cleaned up" a bit in order to avoid as much as possible security issues (in the database).
In that case, the piece {string:a_name} will be replaced with the value of 'a_name' in the array.
$result will then contain the "result" of the query, that is not a php variable, but an object. Next piece.

Koodi [Valitse] Laajenna

$my_result = $smcFunc['db_fetch_assoc']($request);
With db_fetch_Assoc you "read" the object and "convert" it in something that you can use in php that is assigned to $my_result.

Koodi [Valitse] Laajenna

$smcFunc['db_free_result']($request);
Clean up some memory.

Koodi [Valitse] Laajenna

print_r($my_result);
Your result.

Now, how to get only one value.
In that case, I assume that for the name "Jamal" you may have several rows in the database, then it may be a bit tricky, because it may depend on which one exactly you are looking for (the first, the last, none in particular, etc.).
Usually you can use "LIMIT x" (it may be a bit more complex, but for now if you don't need it let's use the simple one) to reduce the number of rows read from the table.
So your query may looks like:

Koodi [Valitse] Laajenna

$result = $smcFunc['db_query']('', '
SELECT f_name
FROM {db_prefix}rpg_characters
WHERE member_name = {string:a_name}
LIMIT 1',
array(
'a_name' => 'Jamal'
)
);

[MrPhil]

mmm..."it doesn't work" means nothing.

My guess would be that he expected it to be a string of the first name, and not a resource.