Omnimaga
General Discussion => Technology and Development => Web Programming and Design => Topic started by: Eiyeron on February 18, 2014, 03:48:04 am
-
Hi everyone! I'm making my own blog framework, and I'd like to know the best way to store post's content. I planned for storing the content in files because I plan to support Markdown editing then updating the resulting php, but won't that be faster in a SQL Database? (storing the MArkdown sources externally then storing the result in a DB?)
Thanks in Advance.
Post not working but the style is defined. (http://geekbros.tk/~eiyeron)
-
Well, I would go for mySQL as it is easy to implement with PHP and mySQL is fun.
But then again, i wrote for withg a blog which uses system-msg as a backend which uses markdown, it can be found here: http://withg.org/blog/
-
Either MySQL/MariaDB or sqlite will probably do.
-
I personally use filesystems for storing articles, but I use mysql for all other things. This is mainly because my own framework was made back when I didn't known mysql. If I rewrote it from scratch I would definitely put everything in the database. No messing around with htaccess files nor directory whitelisting. Mysql works great with php.
-
Well, I would go for mySQL as it is easy to implement with PHP and mySQL is fun.
But then again, i wrote for withg a blog which uses system-msg as a backend which uses markdown, it can be found here: http://withg.org/blog/
Well, system-msg already stored to flat files, you just wrote a web interface for it instead of using it as a backend.
-
I really suggest storing them in your database. The Blob type can contain just about anything, and it's much easier to manage your permissions in the database than using the filesystem.
And considering it's a blog post, it should be stored in a markup language (HTML, BBCode, etc.) so it will probably be better to store it in a text-based type rather than a blob.
-
Well, I would go for mySQL as it is easy to implement with PHP and mySQL is fun.
But then again, i wrote for withg a blog which uses system-msg as a backend which uses markdown, it can be found here: http://withg.org/blog/
Well, system-msg already stored to flat files, you just wrote a web interface for it instead of using it as a backend.
Yeah, if it wouldn't already exist as files, i would've have probably gone for database.
-
I'm already using a DB. I just use it to link to the files.
How do you store/get the blob with Maria/MySQL?
-
well, storing would be then like
INSERT INTO `blog_posts` (post,whateverOtherColumnsYouWantToSet) VALUES ('%s','%s')
Well, substituting %s with the corresponding value of cource
Updating would be like this:
UPDATE `blog_posts` SET post='%s' WHERE postId=%d
If you want to update more columns, just add them with a comman, like this:
post='%s',name='5s'
And to fetch:
SELECT post,name,whatever FROM `blog_posts` WHERE postId`%d
I hope that quick guide helped a tad :)
-
It seems quite strange to use a DB for storing things but why not. Thanks.
EDIT : Now I store the posts in a value directly in the DB, thanks for your advice. I need to implement now a back-end interface.
(UPDATE SET text=$string WHERE id = $id);
-
nonononono, NEVER put php variables directly into a query, evil things can be done then easilly, like SQL injection and stuff like that.
Allow me to copy-pasta you a function i wrote that does all that escaping stuff automatically:
class Sql{
private $mysqliConnection;
private $queryNum;
public function __construct(){
$this->queryNum = 0;
}
private function connectSql(){
if(!isset($this->mysqliConnection)){ //if no connection yet we create one
$this->mysqliConnection = new mysqli('localhost','sql_user','sql_password','sql_database');
if ($this->mysqliConnection->connect_errno)
die('Could not connect to SQL DB: '.$this->mysqliConnection->connect_errno.' '.$this->mysqliConnection->connect_error);
$this->mysqliConnection->autocommit(true);
}
}
public function query($query,$args = [],$num = false){
$this->connectSql(); //connect to sql
for($i=0;$i<count($args);$i++) //escape arguments
$args[$i] = $this->mysqliConnection->real_escape_string($args[$i]);
$result = $this->mysqliConnection->query(vsprintf($query,$args)); //insert arguments in query and execute it
$this->queryNum++; //add one to the query counter
if($this->mysqliConnection->errno==1065) //empty
return array();
if($this->mysqliConnection->errno!=0)
die($this->mysqliConnection->error.' Query: '.vsprintf($query,$args));
if($result===true) //nothing returned
return array();
$res = array();
$i = 0;
while($row = $result->fetch_assoc()){
$res[] = $row;
if($num!==false && $i===$num){ //if the user set an element then we only reaturn that one
$result->free();
return $row;
}
if($i++>=150) //we don't want php to mem overflow
break;
}
if($res === []){ //if the result is empty we create a dummy array with the assoc fields, each containing NULL
$fields = $result->fetch_fields();
for($i=0;$i<count($fields);$i++) //create each field and set it to NULL
$res[$fields[$i]->name] = NULL;
if($num===false) //if we didn't set an element we expect it to be an array in an array
$res = array($res);
}
$result->free();
return $res; //return the result
}
}
$sql = new Sql();
Ok, this has also some features you may actually won't need, lol.
The key is to do a mysqli_real_escape_string() on all your data before putting it in, and be sure, that if you enter a string in SQL you put single quotes around it.
Example usage:
$res = $sql->query("SELECT * FROM `blog_posts` WHERE id=%d",[$number]);
$res will contain now an array with all results, but, as in this case, you only need the first result, I would do something like:
$res = $sql->query("SELECT * FROM `blog_posts` WHERE id=%d",[$number],0);
The 0 tells it to only grab the first result.
If the result is empty that function will still create array stuff, but put NULL everywhere in.
Oh, another example for string stuff:
$sql->query("INSERT INTO `blog_posts` (post,name) VALUES ('%s','%s')",[$post,$name]);
Ok, maybe that was a bit overkill now, you can write your own functions too, just make sure you _real_escape_string() everything you put in and use single quotes around strings! And if you put in an int make sure it IS an int, you can do that by pre-fixing the php variables with (int), example: (int)$id
-
I use prepared queries with PDO! :D
But your function seems to be inetresseting, I should read it later to understand it better.
-
oh, ok, nice! But i would still use the single quotes on strings :P
-
oh, ok, nice! But i would still use the single quotes on strings :P
Edited after you replied --"
-
Hi back!
Currently trying to implement correctly back-side, I need to support sessions. How do I manage them the best way? Don't remember my lessons... --"
-
<?php
session_start();
$_SESSION['lose'] = 'THE GAME';
echo $_SESSION['lose'];
?>Does that help your memory? ;)
-
Well thanks for the discussion, I forgot that $_SESSION was actually stored server-side and not sent as _GET and _POST. I'll to redesign a lot of things! (Better posting implmentation yay!)
-
there is also $_COOKIE for cookies, $_SERVER for information about the current instance (like remote IP), and $_REQUEST which is $_GET $_POST and $_COOKIE combined.
Might have missed some >.<
-
Welp, if you want to get some news about my battle against PHP, it seems my project is working nicely at the moment. I'm still working between refactors and database redisign, but it's still fine.
For the moment.
-
Looks kinda nice so far :D
However, on some monitors and contrasts, it might be hard to see the divs colors and stuff since everything will just look like black.
-
Yeah, on my phone the red looks black. But if I choose brighter colors, I think that'll make it ugly. I'll to remake my color swatches.
The R-A logo will change, it never looked in place with the blog anyway. One graphical change to do too. I don't know how it'll look after then.
-
Looking nice, gladwe could help you out some :)
-
If you want to help me more, I woul'd be glad. i'm far from having finished it! ^^
EDIT : With your class, Joins doens't work at all...
res = $sql->query("SELECT `B.bi, `B.bn`, `B.bs` ".
"FROM `Blah B` LEFT JOIN `Ah_Blah` A ON A.bi = B.bi ".
"WHERE A.ai = `%s`;", [$_SESSION['ui']]);it only works without `quoting`
EDIT : Nailed it
res = $sql->query("SELECT `B`.`bi`, `B`.`bn`, `B`.`bs` ".
"FROM `Blah` B LEFT JOIN `Ah_Blah` A ON `A`.`bi` = `B`.`bi` ".
"WHERE `A`.`ai` = %d;", [$_SESSION['ui']]);
-
As it is SQL you don't need to join, it doesn't care about line breaks and weird tabs ;)
-
As it is SQL you don't need to join, it doesn't care about line breaks and weird tabs ;)
Explicit joins are preferable in sql though. Implicit joins can be slower.
-
As it is SQL you don't need to join, it doesn't care about line breaks and weird tabs ;)
Yeah, I know for tabs, I made it readable after it worked.