[SOLVED] Fetch data from mysql table

[spyer]

Good day people,

 

i have a question that's been annoying me for a while now.. and i could not find an answer to it.

 

here it goes... let's say i have a table [language] in my database [lang]

 

it has four columns [languageid][phraseid][title][text]

 

so for example.. the values would be like so

 

// ##### languageid ###### phraseid ######## title ######### text
// ##### ____1____ ###### ___1___ ###### username ###### Username
// ##### ____1____ ###### ___2___ ###### password ###### Password

 

NOTE: phraseid is "auto_increment" and obviously a "Primary".

 

you've probably guessed what i want to do with it... for those who didn't,,, i want to use the [text] field in the [language] table to print phrases in an HTML page to use them as phrases...

 

for example:

 

<table>

  <tr>

    <td>$language[username]</td>

    <td><input type="text" name="name"></td>

    <td>$language[password]</td>

    <td><input type="password" name="pass"></td>

  </tr>

</table>

 

 

that should come out like...

 

          ____________________
Username |____________________|
          _____________________
Password |____________________|

 

 

what code should i use,, how to call a certain field in a certain table..

 

i was thinking like $language[text][1] with mysql_fetch_rows or something like that.. i could not find a way...

 

so.... please help

 

i would really appreciate it.

[Psycho]

If all "text" fields are page specific, e.g. the name field only appears on the login page, then you could add another column for a page identifier. Then you could just query for all rows that match the page your are creating. If taht is what languageID represents, then you could go that route. Not sure what the purpose of that field is based upon your example above since both rows have the same value.

 

But, that wouldn't be wise in my opinion since you might want to use "name" on different pages and don't want to have multiple "name" values to be edited. Int hat case you just need to determine which text fields you need on a particular page and query for those.

 

Let's assume that the "name" field has an ID of 3 and the "password" field has an ID of 8. Then you just need to run the following query on your login page:

 

SELECT * FROM language WHERE phraseid IN (3,

[spyer]

thanks for your replay m8,,

 

they don't really have the same value... in English yes maybe... but in French or Chinese it would be different...

 

Title->username & text->User name===> English

Title->username & text->Nom d'utilisateur===> French

Title->username & text->用户名===> Chinese

 

since i have two ID fields:

[languageid] -> [1 = English] -> [2 = French] -> [3 = Chinese] ... etc

[phraseid] = 1 -> [title = username] -> [text = Username]

[phraseid] = 87 -> [title = username] -> [text = Nom d'utilisateur]

 

that means,, the [languageid] would be determinde earlier in the sessions data.. so:

if the:

[languageid] = 1 the [title] -> username = User name

[languageid] = 2 the [title] -> username = Nom d'utilisateur

..... and so on.

 

regarding the {name} field.. i'm using it as an example..

 

i'm sorry that my English is not that good  .. i hope you got my point 

[duclet]

You can try something like this:

<?php
$sql = 'SELECT title, text FROM language WHERE languageid = "ENTER THE LANGUAGE ID HERE"';
$query = mysql_query($sql);

while($row = mysql_fetch_assoc($query)) { ?>
    <td><?= $row['title']; ?></td>
    <td><input type="text" name="name" value="<?= $row['text']; ?>"></td>
<?php } ?>

[spyer]

You can try something like this:

<?php
$sql = 'SELECT title, text FROM language WHERE languageid = "ENTER THE LANGUAGE ID HERE"';
$query = mysql_query($sql);

while($row = mysql_fetch_assoc($query)) { ?>
    <td><?= $row['title']; ?></td>
    <td><input type="text" name="name" value="<?= $row['text']; ?>"></td>
<?php } ?>

 

i'm sorry,, that's not what i ment...

 

languageid -> is to determine which language

phraseid -> is to determine which phrase

title -> is just a name which i can use "IF POSSIBLE" to call a text

text -> is the text i want to print

 

what i'm looking for is... to put something like this $language['text'][10] .. and that should output the text "something" with the phraseid number 10

 

so if the phraseid 10 = Logout .... $language['text'][10] = Logout

and if the phraseid 11 = Login .... $language['text'][11] = Login

 

i'm sorry for any confusion

[rhodesa]

You can populate an array of phrases at the beginning:

<?php
  $lid = 1; //Language ID figured out somewhere else

  //Run this before every script
  $sql = 'SELECT title, text FROM language WHERE languageid = "{$lid}"';
  $query = mysql_query($sql);
  $phrases = array();
  while($row = mysql_fetch_assoc($query))
    $phrases[$row['title']] = $row['text'];

  //In your script
  echo $phrases['username'];
?>

[spyer]

You can populate an array of phrases at the beginning:

<?php
  $lid = 1; //Language ID figured out somewhere else

  //Run this before every script
  $sql = 'SELECT title, text FROM language WHERE languageid = "{$lid}"';
  $query = mysql_query($sql);
  $phrases = array();
  while($row = mysql_fetch_assoc($query))
    $phrases[$row['title']] = $row['text'];

  //In your script
  echo $phrases['username'];
?>

IT WORKED

 

i can't thank you enough guys,, i really needed that... and you've been so helpful

thank you very much

[Psycho]

If your list of "phrases" gets really long, you can filter your results specific to a page by selecting only the phrase id's specific to a page:

 

$sql = 'SELECT title, text FROM language WHERE languageid = "{$lid}" AND phraseid IN (3,8,9)';

 

Where "3,8,9" represent the phrase IDs that you need on any particular page.

[spyer]

If your list of "phrases" gets really long, you can filter your results specific to a page by selecting only the phrase id's specific to a page:

 

$sql = 'SELECT title, text FROM language WHERE languageid = "{$lid}" AND phraseid IN (3,8,9)';

 

Where "3,8,9" represent the phrase IDs that you need on any particular page.

 

i got your point,, but if a certian page has more than 20 phrases... useing {AND phraseid IN (3,8,9)} would be too long.. don't you agree

[Psycho]

If your list of "phrases" gets really long, you can filter your results specific to a page by selecting only the phrase id's specific to a page:

 

$sql = 'SELECT title, text FROM language WHERE languageid = "{$lid}" AND phraseid IN (3,8,9)';

 

Where "3,8,9" represent the phrase IDs that you need on any particular page.

 

i got your point,, but if a certian page has more than 20 phrases... useing {AND phraseid IN (3,8,9)} would be too long.. don't you agree

 

No, I don't agree. It would be more efficient to query for only the 20 phrases used on that page than to query for ALL the phrases in your database and then pull them into an array when you are only going to use 20 of them. As more people are using the site, you will have a lot of wasted memory to hold those array values in memory as the pages are being constructed.

 

Another option, which would take a little more work, would be to create an association table to identify which phrases appear on which page. The table would just need two columns: pageID and phraseID. Then you could pull all the phrases for a page (and only those phrases) with a simple query identifying just the page:

 

SELECT title, text

FROM language

  JOIN phraseAssoc ON language.phraseid = phraseAssoc.phraseID

WHERE languageid = "{$lid}"

  AND phraseAssoc = "{$pageID}"