Jump to content

Querying A Db

army20
 Share

Recommended Posts

army20

army20

Posted
Posted

Hi guys,

I'm familiar with mysql DB and php but I'm now looking to make a simple application to query and show result from a sql DB.

What language is most recomanded: Visual studio, C++, Java or other?

Also if someone can provide a webpage showing how to do this. I've searched on the net but I don't even know how to name this fonction... The box is suggesting results while i'm typing.

thanks

-M

Link to comment
Share on other sites

jdoe

jdoe

Posted
Posted

With VB you can do this with DAO or ADO and execute an SQL command.

SELECT * FROM Table WHERE ...bla bla bla

Search for Access database and you'll get full of sample code using DAO.

:)

Link to comment
Share on other sites
purewaveform

purewaveform

Posted
Posted

couple of questions

1. Which database are you trying to connect to, specifically?

2. How much programming experience so you have, you say familar with php, but what about the other languages?

3. How familar are you with threads? To have the lookup, it works best with threads.

4. Do you have to write any data back, or are you only going to be reading?

5. Do you want to do this by hand, or by IDE?

6. does it need to be multi-platform?

I can refer you to several places that show you how to do this but you need to tell me the answers to the questions.

Link to comment
Share on other sites
dman

dman

Posted
Posted

I found this simple, generic php code to query mysql db when I was asking the same questions you are. Maybe it will help you as well.

<? 

/*
	Author - Ofri Markus
	Date   - 3/12/03

	This is a generic script to view and modify mysql databases.
	All you need to do to use this script is:
	1. Put it in your site, and call the file "admin.php".
	2. Fill in the connection details on the first line, replace DB_USER AND DB_PASSWORD.
	3. Fill in the database name on 2 lines, replace DB_NAME.

	The advantage of this script is that is you don't need to 
	modify it to your own scheme. it will work on any scheme.

	I would be happy to receive comments and improved versions of 
	this script to:

	markus_ofri@hotmail.com

	Enjoy!

*/

// Initial connection to the database
$database = mysql_connect("localhost", "DB_USER", "DB_PASSWORD");
mysql_select_db("DB_NAME");

// Because the first time we enter the site we have'nt selected
// a table to view we init the number of rows in the current table
$rowNum=0;

?>

<html>
<body bgcolor="#ffffff">

<? 

// Get the data of the tables on the scheme
$result = mysql_list_tables ("DB_NAME");

$i=0;
while ($i < mysql_num_rows ($result)) 
{
	$tb_names[$i] = mysql_tablename ($result, $i);$i++;
}

// Check if we got here after pressing submit on the page
if (isset($_POST['submit']))
{
   // If we did press the submit button, we sould view the table that was on the select
   // box
   $submit=$_POST['submit'];
   $table=$_POST['table'];
}
else
{
	// If we didn't get here after pressing the submit button, check if we already
	// viewed one of the tables (and saved it's name on the hidden field hidtable
    if (isset($_POST['hidtable']))
	{
  $table = $_POST['hidtable'];
	}
	else
	{
  $table="<i>not selected</i>";$xnum=0;
	}
}

?>

<center>
<form name=ff method=post action="/admin.php">
<b>Select table</b>:<select name="table">
<? 
for($x=0;$x<$i;$x++)
{?>
	<option value="<? echo $tb_names[$x];?>" <? if (isset($table) && $table==$tb_names[$x]) {echo " selected ";} ?>><? echo $tb_names[$x];?></option>
<? }?>
</select>
<input type="submit" name="submit" value="submit">



<? 

// Check if we pressed the submit button and if we did - fetch the table data
if(isset($submit) || isset($_POST['hidtable'])){
$SQL="SELECT * FROM $table";
$result = mysql_query($SQL);
$xnum = mysql_num_fields($result); 
$rowNum = mysql_num_rows($result); 

// Read all the data in the table
for ($j = 0; $j<$rowNum; ++$j) 
{
	$row = mysql_fetch_array($result);
	$currTable[$j]=$row;
}
}




echo "<b><center>$table contains $xnum fields.<br><br></b></center>";?>

<center>
<table bgcolor=black><tr>
<td bgcolor="#e3e3e3">Offset</td><td bgcolor="#e3e3e3">Field Name</td><td bgcolor="#e3e3e3">Field type</td></tr>


<? 
// Get the data about the primary keys and the numeric fields in the table
for($x=0;$x<$xnum;$x++)
{
	$name[$x]=mysql_field_name($result,$x);$type[$x]=mysql_field_type($result,$x);
	$currField = mysql_fetch_field($result,$x);
	$key[$x]=$currField->primary_key;
	$numeric[$x]=$currField->numeric;
?>

<tr>
<td bgcolor="white"><? echo $x;?></td>
<td bgcolor="white"><? echo $name[$x];?></td>
<td bgcolor="white"><? echo $type[$x];?></td>
</tr>
<? }?>
</table></center>
<br>

<?

// Check to see if there was an update to a row
for ($j = 0; $j<$rowNum; ++$j) 
{
    if (isset($_POST["update".$j]))
    {
  // Make an sql update query
        echo "<center>There was an update to row $j</center>";
        $sql="update $table set ";
        for ($i = 0; $i < $xnum; ++$i)
        {
 	 if ($numeric[$i]==1)
 	 {
             $sql.=$name[$i]."=".$_POST["$name[$i]".$j]." ";
 	 }
 	 else
 	 {
             $sql.=$name[$i]."='".$_POST["$name[$i]".$j]."' "; 	 
 	 }
 	 
 	 if ($i != $xnum-1) {$sql.=",";}
        }
        $sql.="WHERE ";
  $notFirstKey = 0;
     for ($i = 0; $i < $xnum; ++$i)
        {	
 	 if ($key[$i]==1)
 	 {
    if ($notFirstKey == 0) {$notFirstKey=1;}
    else {$sql.=" AND ";}
           	 $sql.=$name[$i]."=".$currTable[$j][$i];
    
 	 }
 	 
        }
  if ($notFirstKey == 0) {
 	 echo "Table does not have a primary key, not doing anything";
  }
  else {
   	 echo $sql;
         $result = mysql_query($sql);
  }

        
    }
}

// Check to see if there was a delete to a row
for ($j = 0; $j<$rowNum; ++$j) 
{
    if (isset($_POST["delete".$j]))
    {
        echo "<center>There was an delete to row $j</center>";
        $sql="delete from $table ";
        $sql.="WHERE ";
  $notFirstKey = 0;
     for ($i = 0; $i < $xnum; ++$i)
        {	
 	 if ($key[$i]==1)
 	 {
    if ($notFirstKey == 0) {$notFirstKey=1;}
    else {$sql.=" AND ";}
           	 $sql.=$name[$i]."=".$currTable[$j][$i];
    
 	 }
 	 
        }
  if ($notFirstKey == 0) {
 	 echo "Table does not have a primary key, not doing anything";
  }
  else {
   	 echo $sql;
         $result = mysql_query($sql);
  }
        
    }
}

// Check to see if there was an insert of a row
    if (isset($_POST["insert"]))
    {
        echo "<center>There was an insert of a row </center>";
        $sql="insert into $table values (";
        for ($i = 0; $i < $xnum; ++$i)
        {
         $sql.="'".$_POST["$name[$i]"."insert"]."' ";
 	 if ($i != $xnum-1) {$sql.=",";}
        }
        $sql.=")";
    echo $sql;
        $result = mysql_query($sql);
        
    }




///////////////







if(isset($submit) || isset($_POST['hidtable'])){
$SQL="SELECT * FROM $table";
$result = mysql_query($SQL);
$xnum = mysql_num_fields($result); 
$rowNum = mysql_num_rows($result); 

}

?>




<center>
<table bgcolor=black>
<tr>
<?
for ($i=0; $i<$xnum; ++$i)
{
$name[$i]=mysql_field_name($result,$i);
?>
<td bgcolor="#e3e3e3"><? echo $name[$i]; ?></td>	
<? } ?>
</tr>

<? for ($j = 0; $j<$rowNum; ++$j) 
{
$row = mysql_fetch_array($result);
$currTable[$j]=$row;
}
?>


<? for ($j = 0; $j<$rowNum; ++$j) 
{
?>
<tr>
<? for ($i=0; $i<$xnum; ++$i)
{
?>
<td><input name=<? echo "\"$name[$i]".$j."\""; ?> type="text" id=<? echo "\"$name[$i]\""; ?> value=<? $currRow=$currTable[$j]; echo "\"$currRow[$i]\""; ?>></td>

<? } ?>
<td><input type="submit" name=<? echo "\"update".$j."\""; ?> value=<? echo "\"update\""; ?> height="10">
    <input type="submit" name=<? echo "\"delete".$j."\""; ?> value=<? echo "\"delete\""; ?> height="10">
</td>
</tr>
<? } 
for ($i=0; $i<$xnum; ++$i)
{
?>
<td><input name=<? echo "\"$name[$i]"."insert\""; ?> type="text" id=<? echo "\"$name[$i]\""; ?>></td>

<? } ?>

<td><input type="submit" name="insert" value="insert"></td>




</table>

</center>
<input type="hidden" name="hidtable" value=<? echo "\"$table\""; ?>>


</form>
</center>
</body>
</html>

Link to comment
Share on other sites
  • 5 months later...
Araknis

Araknis

Posted
Posted

To connect to MySQL for Visual Studio Ver 6 (VB, C++, etc) you need the MySQL ODBC driver.

If you are using .NET then you need the MySQL .NET driver

If you are using Java then you need the Java JDBC driver.

Here is a link to all the MySQL connectors.

http://dev.mysql.com/downloads/connector/

You then need to use a control like ADO or DAO at least in VB.

You then need to make a connections string.

They execute the SQL commands to update, insert, delete, etc.,

I did not see the link but do you mean Intellisense (suggesting results while typing).

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...