• Home
  • Shell
    • Emacs
    • Perl
    • screen
    • sed
  • Ubuntu
    • VNC
  • Web Development
    • Javascript
    • Joomla
    • MySQL
    • osTicket
  • Windows
    • Gimp

Posts tagged MySQL

Usenet> Newznab backfilling

Jan31
2013
Written by Scott Rowley

I’ve recently been checking out newznab plus. I’ve found that while it’s nice as it slowly builds, it’s a bit annoying not having any older posts already loaded. Fortunately nn+ (nnp, newznab plus) comes with an option for backfilling. The instructions are useful but I’d like to expand on them to make it less of a hassle to update more than one particular group.

Please note that I’ve only been using nn+ for a few days now so there may indeed be easier ways to do things but this is the best way I’ve found so far. For example, I’m not exactly sure what the update_binaries_threaded.php file does but I don’t believe it does what I’m discussing here. I’m guessing from the “threaded” that it simply makes multiple threads of the same process to make the entire task faster.

The following is taken from http://newznab.readthedocs.org/en/latest/readme/#backfilling-groups

Backfilling Groups
Since most usenet providers have 800+ days of retention indexing all that information in one shot is not practical. Newznab provides a backfill feature that allow you to index past articles once your initial index has been built. To use the feature first set the back fill days setting in the group(s) to be backfilled to the number of day you wish to go back, making sure to set it higher than the number of days listed in the first post column. Once set run the backfill.php script in misc/update_scripts. Groups can be backfilled to a particular date using the script misc/update_scripts/backfill_date.php using the syntax:

php backfill_date.php 2011-05-15 alt.binaries.groupname.here

You can use the _threaded version of this script if on linux.

For more information on backfilling, see Update Scripts.

First off, what we need to do is to activate any groups we want. More than likely you’ve already done this. What you’ll want to do in order to backfill those groups is to change the ‘Backfill Days’ entry. This can be done through the webpage but if you are wanting to backfill all of them then this following command will make it much faster than manually clicking through a dozen or more groups.

You’ll need to be on the command line and then log into mysql

mysql -u root -p
Enter password:
mysql> use newznab; (or whatever your database name is)
mysql> update groups set backfill_target=365 where active=1;

You have now just changed all active groups to have a backfill of 365 days (1 year). Now we just need to run our backfill_date.php on each group. If we’ve got dozens of groups or more though this can be tedious to repeat over and over with each group. So instead we are going to get a list of all the active groups by running the following command:

mysql -u root -p newznab -e 'select name from groups where active=1;'> groups.txt

You can now go into the groups.txt file and remove and additional groups you want or just leave it intact if you want everything.

Finally we want to run the php backfill.php command to backfill each of our selected groups back 365 days (or whatever number you’ve selected, alternatively you can also skip updating the database with the amount of days to backfill and simply substitute the backfill_date.php as noted from the link.)

Normally this command would be run like so for a single group:

php backfill.php alt.binaries.groupname.here

But this is much too slow for multiple groups so we are going to use a ‘for’ function with our data from the groups.txt file we created.

for group in `cat groups.txt`; do php backfill.php $group; done

If you are like me and like to see your code before it runs then simply add an echo beforehand to view the results and then remove it when you are ready:

for group in `cat groups.txt`; do echo "php backfill.php $group"; done

The use of the backticks (`) around ‘cat groups.txt’ tells it to run the command in a subshell and then use the information from that back in the shell command.

Posted in BASH, PHP, Ubuntu - Tagged backfill, backfill days, BASH, for, groups, newznab, newznab plus, nn+, nnp, php, update, usenet

Web Dev> Obtaining Google PageRank

Jun06
2012
Written by Scott Rowley

As a part of Search Engine Optimization (SEO) you may want to be aware of your websites (or clients websites) page rank on Google. After some researching I’ve found the following solutions to be best. I’ll be presenting a few options here, first we’ll cover how to look it up on prchecker.info. Second we’ll be covering how to look up the page rank using a perl script and finally we’ll cover how to look up your page rank with the perl script and update a mysql database with the information (for long term tracking and/or tracking multiple websites.)
READ MORE »

Posted in Perl, PHP, SEO, Web Development - Tagged database, google, page, page rank, Perl, php, prchecker.info, rank, script, search engine optimizaiton, SEO

Web Dev> PHP function, check if a MySQL table exists

May01
2012
Written by Scott Rowley

Note: This is NOT original sudobash.net code. This has been copied over from ElectricToolBox.com, full credit goes to him/her (I couldn’t find a name anywhere).

The PHP function below gets passed in a tablename and an optional database name. If the database name is not passed in then it retrieves it using the MySQL function SELECT DATABASE(). It then queries the MySQL information schema to see if the table exists and then returns either true or false.

function table_exists($tablename, $database = false) {
if(!$database) {
$res = mysql_query("SELECT DATABASE()");
$database = mysql_result($res, 0);
}
$res = mysql_query("
SELECT COUNT(*) AS count
FROM information_schema.tables
WHERE table_schema = '$database'
AND table_name = '$tablename'
");
return mysql_result($res, 0) == 1;
}

The PHP MySQL functions are used in the above example. A database connection is assumed and there is no error checking, but you can modify it to utilise whatever database library / abstraction layer you are using in your project and improve how you see fit.

To use the function you’d do something like this:

if(table_exists('my_table_name')) {
// do something
}
else {
// do something else
}

and if you wanted to specify the database name as well (perhaps you are needing to query if the table exists in multiple databases other than the one you are currently connected to), you’d do this:

if(table_exists('my_table_name', 'my_database_name')) {
// do something
}
else {
// do something else
}
Posted in PHP, Web Development - Tagged check, database, dev, exists, function, php, table, web, Web Development

Web Dev> Populate PHP/HTML table from MySQL database

Jan11
2012
Written by Scott Rowley

Hey again all, for this post I’ll be covering how to populate a PHP/HTML table by way of looping through a table in mysql. I’ll be using the sample database provided by http://www.mysqltutorial.org/mysql-sample-database.aspx which has to do with models (cars, planes, ships, etc). Everyone has differing levels of knowledge so I’ll be including some basics as well such as connecting to the mysql database (and closing it later on).

The table we’ll be using in the database is ‘products’. It has the following columns:

productCode - A unique inventory number
productName - Name of the product
productLine - Basic descriptor, 'Motorcycles', 'Classic Cars', etc
productScale - This models scale size
productVendor - Company that built the model
productDescription - Detailed description of product
quantityInStock - Current number of quantity in stock
buyPrice - Listed price on the "website"
MSRP - Manufacturers Suggested Retail Price

If I know I’m going to be using my mysql database in multiple files I’ll always throw the connection in something like a ‘dbconnect.php’ file. Here’s an example:

<?php
mysql_connect(DBHOST, DBUSER, DBPASS) or die(mysql_error());
mysql_select_db(DBNAME) or die(mysql_error());
?>

Now you can include this in every file, or better yet in your header file which will get included everywhere else. So for example in your header.php file you could throw in:

<?php
require_once('dbconnect.php');
?>

Alright, so now you’ve got your connection to your database and the appropriate database selected. We’ll skip over the other content that you want to eventually add and say (for this example) that we want to list all of our models. We’ll look at doing this a few different ways, first off we’ll go simple and just request everything from the database and then we’ll tell php how to spit that all out to us.
READ MORE »

Posted in PHP, Web Development - Tagged connect, html, limit, loop, nest, nested, php, query, result, select, sql, where, while

PHP> Export simple MySQL query to .csv file

Aug08
2011
Written by Scott Rowley

The following will allow you to export your mysql queries from mysql to a csv file that can be opened in several spreadsheet softwares. You may need to change the , (comma) to a ; (semi-colon) depending on your software.

A note for those of you using my osTicket Reports MOD: This is not what I’m using for that.

<?php
$host = 'localhost';
$user = 'userName';
$pass = 'password';
$db = 'databaseName';
$table = 'tableName';
$file = 'export';
$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");
$result = mysql_query("SHOW COLUMNS FROM ".$table."");
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field'].", ";
$i++;
}
}
$csv_output .= "\n";
$values = mysql_query("SELECT * FROM ".$table."");
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j].", ";
}
$csv_output .= "\n";
}
$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
?>
Posted in PHP, Web Development - Tagged csv, dev, development, excel, export, file, libre, office, open, php, web

osTickets> Reports v5.0

Jun13
2011
Written by Scott Rowley


IT’S HERE! REPORTS 6.0 FOR OSTICKET VERSION 1.7

NOTE: If you are running any versions from 2.3 to 4.1 the “Replies per Staff” report is WRONG. I strongly suggest you upgrade to 4.2+

Ok, so after being on the osTicket forum since July 2009 I’ve noticed that one big MOD that everyone wants and never fully gets is reporting. The following is my stab at it.

This MOD has been implemented and tested on 1.6ST and 1.6RC5, please let me know if you run into any issues.

Note: For version 3.3+ you will need to create a scp/reports folder (and make sure its writable by Apache) and place the image (csv.png) into the scp/images folder.


Requirements: MySQL 5
pChart (for use with emailed reports) requires the GD and FreeType PHP extensions.

Note: Reports v2.4+ is compatible with Internet Explorer.

osTickets Reports osTickets Reports
osTickets Reports osTickets Reports
READ MORE »

Posted in osTicket, PHP - Tagged date_add, date_format, date_sub, day, department, interval, mod, modification, month, osTicket, php, report, reports, staff, year

MySQL> Epoch to Unix timestamp

Oct29
2010
Written by Scott Rowley
SELECT from_unixtime(epochEntry) FROM table;
Tagged epoch, timestamp, unix

MySQL> Easy export/import

Oct29
2010
Written by Scott Rowley

The following is a quick and easy way to export and import a mysql database.
Keep in mind if at the new location the database doesn’t exist you will need to create it first.

Export:

mysqldump -u USER -p DATABASE > backup.sql

Import:

mysql -u USER -p DATABASE < backup.sql
Tagged backup, export, import, mysqldump

Corrections? Questions? Comments?

Find an error?
Everything work out great for you?
Have some feedback?
Like to see something added to the article?

PLEASE leave us a comment after the article and let us know how we are doing, or if something needs corrected, improved or clarified.

Thank you!
- The Management

Advertisement

Sudo Bash
By Geeks - For Geeks

Back to Top