Translation method

#1

I am wondering if reading the contents of a text file is faster and better than using a function and a switch. I mentioned in my Backtrace post that i want to translate scientific names into English and other languages. I do not know of any pro methods other than a database. Databases are a pain to me and i’d like to avoid them if possible.

my original idea is a function with a switch:

<?php
function Vernacular($binomial) {
  switch ($binomial) {
    case "Turdus migratorius":
        echo "- American Robin";
        break;
    case "Cardinalis cardinalis":
        echo "- Northern Cardinal";
        break;
    default:
        echo "- no common name";
  }
}
?>

i worry about a switch with hundreds of cases being too much of a load for a server. Thus, i just tried reading from a text file and it also works. However, is this method also a burden on a server?

<?php
  $namefile = "path/to/file/" . $Genus . $Species . ".txt";
  if (file_exists($commonname)) {
    $commonname = file_get_contents($namefile);
  } else {
    $commonname = "- no common name";
  }
?>

of the two methods posted, which one is best for this purpose?
is there an even better option?
i think that storing data in variables is also a burden and arrays can consume alot of memory too, i think so.

Thank you.

#2

Don’t use a switch/case statement. Each case value is evaluated until a match is found or the end is reached (there’s no hashing/indexing to find a jump to point in the code), so they become slow for values near the end when you have 100’s of cases. Also, switch/case statements are intended when you are doing different processing based on the input value, not for mapping input values to output values.

The file method leaves you with hundreds of files and if you do translate to language(s) other than English, you will be faced with copying and then editing hundreds of files. You will also be faced with backing up all these files and uploading them all or moving them all between servers.

If your experience with databases was bad due to the amount of code it may have taken in the past, php has an extension, PDO, that’s very simple to use. Also, you can store more than just the name translation in a database. You can then perform searches on any of the information stored in the database. Another advantage of using a database would be you can use a case insensitive character set. With the switch/case, file, or array method, you will have to normalize the values since any comparison will be case sensitive.

If you are not going to migrate to a database, use a lookup/map array, where the key/index is the $Genus $Species value and the data value is the English name. You can copy the array as a starting point for translating into other languages or make sub-arrays, with the primary index being the language abbreviation. Using lookup array(s) take less memory than the switch/case method since all the opcodes for the language structure would not be present, only the key/index and data values.

#3

Hello phdr, Thank you for the lovely post. Very informative post. I know that a database is the best solution here. You are right about me: i learned html, css, javascript (dhtml), qbasic, asp, very basic php, very basic console app c++, sql and database programming in the 90s. I have not kept up with technology since maybe 2003. I just started leaning css3 two years ago. If you remember css3 existed many years ago but support was minimal, thuis i had no reason to learn it back then. I was a hardcore table layout designer. Two years ago i decided to study div and span layout and now i am enjoying it. I also like the responsiveness of modern design. My point is that all of my database knowledge is outdated. I will start reading about pdo today. I also need to brush up on my database interaction with php. I am still a beginning php programmer. I will make a better effort to learn all of this.

Thank you and i’ll let you know how it goes with a database.

#4 
```
<?php
  $namefile = "path/to/file/" . $Genus . $Species . ".txt";
  if (file_exists($namefile)) {
    $commonname = file_get_contents($namefile);
  } else {
    $commonname = "- no common name";
  }
?>

i also noticed an error in my original post regarding the file_exists statement: i misplace the $namefile variable. I’ve corrected the code. I just want to fix it in the event that someone with less experience than me wishes to learn from it.

#5

I’m trying a database with pdo but it’s not going well.

I get an error regarding the usage of prepare:

Fatal error: Uncaught Error: Call to a member function prepare() on string in
C:\xampp\htdocs\testdb.php:11 Stack trace: #0 {main} thrown in C:\xampp\htdocs\testdb.php on line 11
line 11: $query = $db->prepare('SELECT EN FROM genus WHERE LA = $Family');

my testdb.php file

<?php
$host = '127.0.0.1';
$db   = 'avesen';
$user = 'test';
$pass = 'test';
$charset = 'utf8mb4';
$Family = "Paridae";

$dbh = new PDO('mysql:host=localhost;dbname=avesen', $user, $pass);

$query = $db->prepare('SELECT EN FROM genus WHERE LA = $Family');

echo $query;

?>

further, phpmyadmin complains about the lack of an index. do i need an index for this db?

#6

You are trying to run the method prepare on the string ‘avesen’, the db name. Just change the code to use the PDO object in $dbh->prepare('SELECT...'); :slight_smile:

#7

Thanks, Jim. I changed my code but nothing is visible (blank page).

I suspect that i have a problem with the database. I used phpmyadmin within xampp to create the database and table. I will try to build a new database using a console instead. However, i had my stitches removed today and it is Friday. I always make three-to-five homemade pizzas on Fridays, so i will not be able to try a new database until later in the day. Maybe around 8pm or so. I’ll try again later and hopefully it works.

#8

I’ve decided to try a database now instead of later. The database is working and i can query it via console:

SELECT english FROM genera WHERE latin = 'Turdidae';

returns: Thrushes.

The php code returns:

Object of class PDOStatement could not be converted to string

i understand this to mean that $query = $dbh->prepare(…) does not contain a string, so i cannot echo the contents echo $query;

How do i capture the result from MariaDB into a variable for use in a php echo statement?

<?php
$user = 'root';
$pass = '';

$dbh = new PDO('mysql:host=127.0.0.1; dbname=avesenus', $user, $pass);

$query = $dbh->prepare('SELECT english FROM genera WHERE latin = \'Paridae\'');

echo $query;
?>

any suggestions?

#9

Aha! the query returns an array. I had to think about it a few minutes. an array has a key and a value so use the key to get the value? yes.

however, i also needed to use execute and fetch, which wasn’t mentioned here. Now my code looks like this:

<?php
$user = 'root';
$pass = '';

$dbh = new PDO('mysql:host=127.0.0.1; dbname=avesenus', $user, $pass);

$query = $dbh->query('SELECT english FROM genera WHERE latin = \'Paridae\'');
$query->execute();
$response = $query->fetch(PDO::FETCH_ASSOC);

print_r ($response);
echo "<br />";
echo $response['english'] . '<br />';
?>

now it works. i made the php but where is the help? i guess it’s phphelpyourself.com :smile:

just joking! i’m in a good mood now. I finally got it working.
Thank you, Jim. I hope that everyone has a pleasant day.

#10

Looks like you all have this handled!

#11

Hello astonecipher, everyone is a big help. I appreciate all of you very much. I’m still a beginner, so i always appreciate patience.

I live in Berlin, Germany so i the time here is almost 10pm. I made some homemade pizzas today and i was thinking about this code: i forgot about using a variable. Later i tried a variable and it didn’t work. I did some research and i discovered the bindParam method of using a variable.

$sth = $dbh->prepare('SELECT english FROM genera WHERE latin = :family');
$sth->bindParam(':family', $Family, PDO::PARAM_STR, 32);

Thus, i can now use a variable to fetch the result and i don’t have to ask about it. I consider this topic resolved.

Thank you everyone!

#12

You should use implicate binding, where you supply an array of values to the ->execute([…]) method call. This will eliminate the need for the explicate bindParam() statement(s).

I also recommend that you form the sql query statement in a php variable, then supply it to the ->prepare() method call. This will aid in debugging (you can echo/log the sql query statement) and you will also see that you are repeating the same php statements, which can then be simplified by extending the PDO class with your own methods to perform common operations.

#13

Hello phdr, Thank you for all of the help with this thread. I appreciate you very much.

So, i thought about what you mentioned and i remembered reading about binding with a question mark, thus i assume that this is implicate binding. I’ve adjusted my code and tested it successfully!

hopefully you will confirm that this new code is correct:

<?php
$user = 'root';
$pass = '';
$Family = 'Paridae';

$query = 'SELECT english FROM genera WHERE latin = ?';
$dbh = new PDO('mysql:host=127.0.0.1; dbname=avesenus', $user, $pass);

$sth = $dbh->prepare($query);
$sth->execute([$Family]);
$field = $sth->fetch(PDO::FETCH_ASSOC);

echo $field['english'];
?>

also, please excuse the use of root user and blank pass. I am testing in xampp = this-> will be changed.

#14

The ? place-holder is a positional place-holder, i.e. its position in the query is significant, and needs to match the order of the data in the array being supplied to the ->execute() method. This has nothing to do with explicate/implicate binding. The other type of place-holder is a named place-holder and is position independent. One or the other type of place-holder (they cannot be mixed in one query) can be used with either kind of binding, but the ? place-holder results in the least amount of typing.

If you set the default fetch mode to assoc when you make the database connection, you won’t have to specify it in each fetch statement, which again results in the least amount of typing.

You should also set emulated prepared queries to false and set the error mode to exceptions when you make the database connection.

1 Like
#15

Hello phdr, i didn’t consider errors while creating this database connection. I admit that your last post is a bit complex for me. I’m not experienced enough to know how to proceed. When i read the values from a file, i could just use if else to deal with errors. A database with PDO is a whole different beast to slay. I looked into the matter and i see try catch method but i have no knowledge of this in practice. I tried to code it like an if else. Here is what i have now:

<?php
$user = 'root';
$pass = '';
$att = array(
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
  );
$Family = 'Paridae';

try {
    $query = 'SELECT english FROM genera WHERE latin = :family';
    $dbh = new PDO('mysql:host=127.0.0.1; dbname=avesenus', $user, $pass, $att);

    $sth = $dbh->prepare($query);
    $sth->execute(array(':family' => $Family));
    $field = $sth->fetch();

    echo $field['english'];

} catch (PDOException $e) {
    //log error
    echo $Family;
}

?>

is this correct? you mention that i should include the parameters in the execute statement. I couldn’t get it to work until i used an array. Is this the correct method of executing parameters?

also, how do i deal with errors? should i create a log file and log the error myself via function?

i ran the above code with the database on and off and i don’t see an error. I see the database value or the $Family value, which is what i want. I don’t want users to see app errors.

is this new code acceptable? otherwise, i’d like to know of a better way to do this without having to spend too much time trying to implement a better design. I have yet to include a way to change the variable. I also want to fetch $Kingdom, $Phylum $Class and $Species names, thus :family will have to be altered or i will have to alter the entire pdo statement.

i read about setting prepared queries to false and i wonder if i should be using query instead of prepare. I admit that i do not know exactly why one uses prepare. I interpret the keyword to mean ‘prepare’ an sql statement but i don’t why one should do this versus query.

Anyway, if this code can be better, then i wish to make it better. Otherwise, i am happy to finally be using a database and my code actually works :smile:

#16

Yes. Every place I stated this included that it was an array and your previous code above used an array (the [] is short-cut syntax for an array.) For the case where you weren’t using an array, you should have been getting a php error. Do you have php’s error_reporting set to E_ALL and display_errors set to ON so that php would help you by reporting and displaying all the errors it detects? These two settings should be in the php.ini on your system so that all errors will be reported. Stop and start your web server to get any changes made to the php.ini to take effect.

In most cases you should just let php catch and handle the error (you would not have a try/catch block in your code) where it will use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information. When learning, developing, and debugging code/queries you should display all errors (see the settings mentioned above.) When on a live/public server, you should log all errors (by changing the display_errors setting to OFF and the log_errors setting to ON.) For a data driven design, most database errors are fatal and there’s no point in doing more than displaying/logging the error and halting execution (which will all happen automatically if you let php catch and handle the exception.) The exception to this is when you are inserting/updating user supplied data and a duplicate value error occurs. This is a recoverable application error. In this case you would have a try/catch block around the execution of the query, detect if the error number is for a duplicate key error (requires defining the data column(s) as a unique index), set up a user error message telling the user what was wrong with the data that was submitted, and continue program execution. If the error is not for a duplicate key, re-throw the exception and let php handle it.

See the error_reporting, display_errors, and log_errors settings mentioned in this reply.

The genera table you have in your example is the ‘defining’ table for the genera values. In addition to providing language translation, it should have an id (auto-increment) column that establishes a genera_id. You would also have defining tables for the other major taxonomic categories - Kingdom, Phylum, Class and Species. When you store data for each actual organism, you would use the ids established in these defining tables. To retrieve data, you would use a JOIN query to get the related values form the different tables.

There are two main reasons for using a prepared query -

  1. To prevent sql special characters in external/unknown data from breaking the sql query syntax (which is how sql injection is accomplished.)

  2. To save the time needed to communicate the sql query statement between php and the database server and the parsing and planning time of the sql query on the database server, when executing a query more than once during a single invocation of your script.

If an sql query has external/unknown data being supplied to it (executed once or multiple times with the same or different input values) use a prepped query. In those few cases where you need to execute an sql query, that doesn’t have any external/unknown data, more than once in a single invocation of your script, use a prepared query. Otherwise, use the ->query() method.

An issue with emulated prepared queries or using the PDO quote() method in your code is, if you haven’t set the character set that php is using to match the character set of your database tables, sql injection is still possible. Also, the emulation isn’t accurate, so there are functional differences between emulated and true prepared queries.

#17

Hello phdr, Thank you for helping me. I appreciate everything.

php.ini, how stupid of me. I forgot about this. I’m too focused on the database and php programming. My developmen computer monitor is being repaired, so i just installed xampp on my old laptop to work while i wait for my development system. Anyway, i forgot to change apache and php settings.

i need read more about database design and using JOIN. I understand what you mean but i need to study more to get it right. Thank you for the explanation.

I am so tired lately, thus, i have not noticed that i named the table for the family as genera. I need to sleep more. The correct table name is ‘family’. I even named the parameter family but still didn’t notice genera was not correct.

One last question about this database: do i need to close the connection? i saw it mentioned somewhere that the connection stays open. The proposed solution:

$sth = null;
$dbh = null;

This makes me wonder about what is really happening. when i connect to the database: if the connection stays open, then isn’t this a problem if i connect several times for different reasons? you don’t have to answer explicitly but even a link to a document about this information will be nice.

#18

Php automatically destroys all variables/resources it uses when the instance of your script ends. So, you don’t need to close the connection in your code. You can destroy the PDOStatement object if you have a need to free up resources as your script runs, but in most cases there’s no real need.

Your script should only make one connection, then use that connection in the remainder of the code. If you are actually making (and closing) multiple connections in your script, the connection is a very time consuming database operation, and since the connection isn’t closed immediately and all the resources freed up on both ends, you actually end up tying up multiple connections for each invocation of your script.

#19

I read a blog about sql injections and the author made it seem as though a connection stays open anytime one connects to a database. I thought it is closed at script end but the vague statement made me wonder. Just to clarify, no i am not opening multiple connections in the same script. I was just thinking that if the author of the blog suggests that all connections stay open, then when i run a login sript connection and later i use this translation script on a species page and i open a connection on a search script everything is open? i wanted to be sure that the script closes it at script end.

Thank you for clarifying this matter. I hope that you have a pleasant night. I’m going to read more about proper database construction and using join…

#20

There are persistent database connections, but they only work on a specific server configuration - php must be running as a server module on a multi-process/multi-threaded web server. When php is running as a CGI (Common Gateway Interface) program (which it how it is normally done on shared web hosting), they don’t persist and instead work as a regular connection.

It’s the connection between the web server and the database server that persists. The php script must still execute the connection code to get a connection. If a free connection exists, it will be returned to that instance of the script. If a free connection doesn’t exist, a new connection will be created, up to the maximum connection limit. When the instance of a php script ends, the connection will be returned to the connection pool.

Ref: http://php.net/manual/en/features.persistent-connections.php Note: the warning about table locking and transactions.

Sponsor our Newsletter | Privacy Policy | Terms of Service