Tag: mysql

Categories
post

Conditional Statements in Mysql (if/case/etc)

I never really thought to ask the question, but was shocked to see you can do simple conditional statements in Mysql. This will make my querying far better than in the past. Plus I can pull quick data when needed instead of writing a script to analyze it.

Example, trying to find out if an order was placed in our callcenter or on the web:

SELECT orderDate, if(ipAddress = ‘XXX.XXX.XXX.XXX’, ‘CallCenter’, ‘Web’) as WherePlaced, count(*) as cnt FROM orders GROUP BY orderDate, WherePlaced

Simple, but only useful if you know about it. Now I do. 🙂

Categories
post

Getting Counts For Groupings From Mysql

My dad “beto” asked me about a topic he would like to use on his website.  I decided to share it publicly.

He has a simple select box that stores a “Yes/No/Maybe” option in a table.  He wants to include the results on a webpage.

I have to assume his Mysql table (let’s call it ReunionAttendance) has at least two columns (SelectBoxValue, GuestsAttending).  Adjust the field names for the actual table columns in your table.

There are many ways to do this.  I am going to do it this way for a demonstration:

<?php
/*  Steps needed
1) Get data from table
2) Put data into a variable
3) Display the table via HTML
*/

/* 1 */
$result = mysql_query(“SELECT SelectBoxValue, SUM(GuestsAttending) as Attending FROM ReunionAttendance GROUP BY SelectBoxValue”);

/* 2 */
while (list($comingValue, $comingCount) = mysql_fetch_row($result)) {
$list[$comingValue] = $comingCount;
}

/* 3 */
?>
<div id=”WhosComing”>
<table>
<thead>
<tr>
<th>Coming?</th>
<th>Total</th>
</tr>
</thead>
<tbody>

<?php
foreach ($list as $comingValue => $comingCount) {
?>
<tr>
<td><?php echo $comingValue?></td>
<td align=”right”><?php echo $comingCount?></td>
</tr>

<?php
}

?>

</tbody>
</table>
</div>

Categories
Programmer's Mindset

Mysql Master / Slave Thoughts

I have been thinking about a way to have isolated machines that I can almost take on and off line very easily.  Here are my thoughts and would love feedback on my “solution”.

I am almost thinking about “spinning” the traditional model.  I will have my Administrative DB(ADB) in a central place.  ADB will be where I pull all reporting, master updates of product and pricing, all orders, etc….

On each individual web server I will have a slave DB (SDB) that will replicate all data from ADB.  The individual web servers will pull information from SDB for pricing, dynamic web pages, etc…  It will also have its own database (ODB) for writes (order information mainly, maybe other statistics as well).  When an order is placed (instead of trying to reach ADB) it will put the information into ODB.

Now I can have a cron job that will pull the data off each ODB to update ADB for reporting purposes.

Why?

  1. Each web server should be able to run without relying on any other machine.
  2. Once the web server’s SDB is up to date, there should be very little delays in price/product changes.
  3. Scalability, simply launch a new server to be up and running.

Does this make sense?
Web servers write to ODB
Administrative server reads from ODB to update itself which replicates to all the SDB (Every slave/web server becomes a realtime backup of ADB)

Here is a diagram of my thoughts.

DB Proof of Concept
DB Proof of Concept
Reblog this post [with Zemanta]