Home >Backend Development >PHP Tutorial >PHP generates SQL_PHP tutorial to create sqlite through one-click Mysql table field through (PDO)
First published: http://www.zzzzy.com/201406053158.html
<span> 1</span> <span>/*</span><span>* </span><span> 2</span> <span> * Mysql表字段一键生成创建sqlite的SQL </span><span> 3</span> <span> * $db 数据库 </span><span> 4</span> <span> * $tbname 表名 </span><span> 5</span> <span> * $is_blob 需要保存的图片二进制数组 array("img1","img2","img3")形式,默认为false </span><span> 6</span> <span> * $retbname 自定义生成sqlite的表名 </span><span> 7</span> <span> * return SQL的语句形式 </span><span> 8</span> <span>*/</span> <span> 9</span> <span>10</span> <span>public</span> <span>function</span> creat_sqlite(<span>$db</span>, <span>$tbname</span>, <span>$is_blob</span> = <span>false</span>, <span>$retbname</span> = <span>false</span><span>){ </span><span>11</span> <span>12</span> <span>$runsql1</span> = <span>$db</span>->query("SELECT * FROM `<span>$tbname</span>` limit 1"<span>); </span><span>13</span> <span>$fields_rows</span> = <span>$runsql1</span>-><span>columnCount(); </span><span>14</span> <span>15</span> <span>$tb_fields_info</span> = <span>array</span><span>(); </span><span>16</span> <span>$tb_string</span> = ""<span>; </span><span>17</span> <span>$dot</span> = ""<span>; </span><span>18</span> <span>19</span> <span>for</span> (<span>$i</span> = 0; <span>$i</span> < <span>$fields_rows</span>; <span>$i</span>++<span>) { </span><span>20</span> <span>if</span> (<span>$i</span> == 1<span>) { </span><span>21</span> <span>$dot</span> = ", "<span>; </span><span>22</span> <span> } </span><span>23</span> <span>24</span> <span>$tb_meta</span> = <span>$runsql1</span>->getColumnMeta(<span>$i</span><span>); </span><span>25</span> <span>$tb_string</span> .= <span>$dot</span>.<span>$tb_meta</span>['name'<span>]; </span><span>26</span> <span>//</span><span>var_dump($tb_meta); </span><span>27</span> <span>28</span> <span> //如果存在数据且字段存在需要取消的字段中</span> <span>29</span> <span>if</span> (<span>$is_blob</span> && <span>in_array</span>(<span>$tb_meta</span>['name'], <span>$is_blob</span><span>)) { </span><span>30</span> <span>$tb_string</span> .= " "."blob"<span>; </span><span>31</span> } <span>else</span><span> { </span><span>32</span> <span>//</span><span>如果不需要保存为二进制</span> <span>33</span> <span>switch</span>(<span>$tb_meta</span>['native_type'<span>]){ </span><span>34</span> <span>case</span> "VAR_STRING": <span>35</span> <span>$tb_string</span> .= " "."varchar"<span>; </span><span>36</span> <span>break</span><span>; </span><span>37</span> <span>38</span> <span>case</span> "LONG": <span>39</span> <span>$tb_string</span> .= " "."integer"<span>; </span><span>40</span> <span>break</span><span>; </span><span>41</span> <span>42</span> <span>case</span> "BLOB": <span>43</span> <span>$tb_string</span> .= " "."text"<span>; </span><span>44</span> <span>break</span><span>; </span><span>45</span> <span>case</span> "TINY": <span>46</span> <span>$tb_string</span> .= " "."smallint"<span>; </span><span>47</span> <span>break</span><span>; </span><span>48</span> <span> } </span><span>49</span> <span> } </span><span>50</span> <span>51</span> <span>if</span> (<span>isset</span>(<span>$tb_meta</span>['flags'][1]) && (<span>$tb_meta</span>['flags'][1] === "primary_key"<span>)) { </span><span>52</span> <span>$tb_string</span> .= " "."PRIMARY KEY"<span>; </span><span>53</span> <span> } </span><span>54</span> <span>55</span> <span>if</span> (<span>isset</span>(<span>$tb_meta</span>['flags'][0]) && (<span>$tb_meta</span>['flags'][0] === "not_null"<span>)) { </span><span>56</span> <span>$tb_string</span> .= " "."NOT NULL"<span>; </span><span>57</span> <span> } </span><span>58</span> <span>59</span> <span>//</span><span>var_dump($tb_meta);</span> <span>60</span> <span> } </span><span>61</span> <span>62</span> <span>//</span><span>生成的sqlite表名</span> <span>63</span> <span>$retbname</span> = <span>$retbname</span> == <span>true</span> ? <span>$retbname</span> : <span>$tbname</span><span>; </span><span>64</span> <span>65</span> <span>//</span><span>创建数据的SQL语句</span> <span>66</span> <span>$creat_data</span> = "CREATE TABLE IF NOT EXISTS {<span>$retbname</span>} (<span>$tb_string</span>);"<span>; </span><span>67</span> <span>68</span> <span>return</span> <span>$creat_data</span><span>; </span><span>69</span> }